Quoting wild card (*) in SELECT statement

Could someone give me a heads up on how to quote * in the select statement??

Here is my code. I am not sure where to put the * and how to quote it.
I am trying to catch the month. For example catch January by using Jan*
----------------------------------------------------------------------
records.Open "SELECT record.amount FROM record WHERE vendor_code = '" & vendor & "' AND period_covered LIKE '" & SelectMonth & "*';", Application.CodeProject.Connection, adOpenStatic, adLockOptimistic, adCmdText
----------------------------------------------------------------------

Thanks a bunch!!


Post your answer or comment

comments powered by Disqus
I am using a criteria entry form as the source of parameters for a select query, and I am using IIF statements in the query so that if the user does not want to specify for that particular field, the search still works based on the other parameters specified. Therefore the statement I am using is;

IIf([Forms]![Criteria Form]![Customer Account] Is Not Null,[Forms]![Criteria Form]![Customer Account],[Data]![Customer Account]).

This works.

However for some of the criteria I want to also use wild cards, so that the user can enter just part of the data, and the relevant records will be returned. I understand that wild cards cannot be embedded into the IIF statement above.

I have tried many methods, and trawled the web for ideas, but I cannot get this to work. I either want the user to enter the wild card themselves ie "100*" returning records starting with "100", or for the query to append the wild card itself.

Can someone help please?

Hi,

My current issue is - I have created a combo box which obtains its list from a query. This works well and my query runs when one of the list items is selected.

I have added an "ALL" to my list but i would like this to act as a wild card when selected.

Can you advise how I make it a select all when "ALL" is selected.

Details are:

form name = Investigate
Combo box = CBO_Franchise

Criteria in query is: [forms]![Investigation]![Cbo_Franchise]

Row source in combo box = SELECT Tbl_ALL_INTEC_DATA.FRN_ID FROM Tbl_ALL_INTEC_DATA UNION select "ALL" FROM Tbl_ALL_INTEC_DATA GROUP BY Tbl_ALL_INTEC_DATA.FRN_ID HAVING ((Not (Tbl_ALL_INTEC_DATA.FRN_ID) Is Null))
ORDER BY Tbl_ALL_INTEC_DATA.FRN_ID;

This results in combo list of:
ALL
EB
TW
NT

Query runs with any of EB,TW or NT selected but not with ALL. I would like all to show all results ie a wildcard.

Many thanks for any help

I have a long date feild I query against and use a wild card in the time part of a long datetime field. Can I do this and how would it be done?

Cheers

Hi,

Can I use wild-card in the OpenForm's WhereCondition? I've tried this but doesn't work:

DoCmd.OpenForm("Form_Name", , , "First_Name = 'Alan*'")

I want the form to show all the records having "First_Name" starting with "Alan"
please help!

Thanks in advance.

Hi everybody
I'm newbie in access and I wanna know how we can use a Parameter in Select statement (SQL view)?
( actually I want to get a number from user and use it for selecting some random records)
tanks in advance

Please Help!!!

I am trying to use a wild card in code but am unable to do so.... The three Access books

and online info says I can BUT....

I am using a laptop with Windows 7 and Access 2007.

I can open a file, and a Query, and an SQL statement - No problem. BUT as soon as I

add a wild card in it I can no longer open the Query or run the SQL statement.

My code is

code block/

Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set CurConn = CurrentProject.Connection
Dim SqlStr As String

SqlStr = ("SELECT Description FROM tblDay WHERE Description Like 'Online Banking transfer from

CHK 2612 Confirmation# 5397372507'") '== (Works)

SqlStr = ("SELECT Description FROM tblDay WHERE Description Like 'Online*'") '== (Does not)

rst.Open SqlStr, CurConn, adOpenKeyset, adLockOptimistic, adCmdText


End Code Block/


As is with the * I do not get an error - But I niether get any results???

I am trying to pull (sort) only some items in a table. Like ALL online transfers.


I hope someone knows how I can use a wild card - Please.

I am having an issue with a select statement containing a LIKE clause where the record set returned is empty, but if I Debug.Print the select statement and copy it to the SQL view of a query and run it there, then it returns a ful set of records.

Record.Open "SELECT * FROM qryVeneerWidths WHERE ((qryVeneerWidths.GradingRule) Like '" & [Forms]![frmDateTime]![cboDefault] & "*" & "')", db, adOpenKeyset, adLockOptimistic

In a query both of these return records
SELECT * FROM qryVeneerWidths WHERE ((qryVeneerWidths.GradingRule) Like '*')
SELECT * FROM qryVeneerWidths

In code, as a record set, only the second one does.

Can any one tell me what the problem with the LIKE statement in the recordset Select clause is.


Thanks

Richard

Can you use a wild card (e.g. *) in conditional formatting. I have a report that has prirority codes. I want to highlight any that are *1a with yellow and any *1b green. So for example, any items that have a priority code I1a, O1a, P1a, Z1a, etc would be highlithted yellow and any I1b, O1b, P1b and Z1b would be highlighted green. I can not seem to get * to work as a wild card in conditional formatting. I have tried "*1a" and *"1a" but neither seems to work. Any suggestions?

Thanks,
Jim

Hi All,

Hope someone can help. I have a database with a form where a user inputs a customer number to update another database. What I want to do is put some validation on the customer number field where it checks if that customer number exists in the other database. If it doesn't I want a messsage to appear saying Invalid Customer Number and set the customer number field to blank.
To do this, I've put the following vba code in the after update event of the customer number field:
Private Sub CustNo_AfterUpdate()
If Me.CustNo Not In(Select[CustDetails].[CustomerNo] from [Cust Details])Then
MsgBox "This is an invalid Customer Number. Please input a correct Customer Number."
Me.CustNo = ""
End If
End Sub

However I think the syntax must be wrong as I get the following message with the In highlighted from If Me.CustNo Not In(:
Compile Error:
Expected: Expression

Can you do an If field not in (select... in vba and if so how?

Any help gratefully received
Thanks

Hi guys,

Can someone quide me how can i sum two expression collumns into a statement?

a=1
b=2

SELECT 1 AS a, 2 AS b

ok so far. The problem is when i add one more collumn to store the sum. I can't figure whow am i going to phrase into the select statement. The below returns error.

Total=a+b

SELECT 1 AS a, 2 AS b, a+b as total

Thank you

I HAVE A FIELD I WANT TO BE ABLE TO ENTER A PARAMETER FOR WHEN I RUN THE QUERRY. BUT IT NEEDS TO INCLUDE A WILD CARD AT THE END OF DATA ENTERED. HOW TO DO THIS?

I have a form that has approx. 50 textbox fields that display the prices of parts. All of these field names end in Prs, (Table1Prs – Table50Prs). I would like to have a text box displaying the total of all 50 boxes. I know you can use =[Table1Prs]+[Table2Prs]+ect. this would be very long.
Can or how can you use a wild card in this statement? *Prs?

Thanks gMAC

Hi,
i am reading the queries from a text file and am executing in the Access data base.

in module, i wrote as

Quote: Sub macro1()
Dim sFileText As String
Dim strFinalString As String

iFileNo = FreeFile
Open "c:myScript.txt" For Input As #iFileNo
Do While Not EOF(iFileNo)
Input #iFileNo, sFileText
strQuery = Replace(sFileText, "65", ",")
DBEngine(0)(0).Execute strQuery

Loop
End Sub and my "myScript.txt" contains a query like....
insert into student(sno65sname) values(4365'John')

while reading the query from text file, i am unable to read the full query due to comma (it is getting splitted when ever comma comes). so instead of comma i used 65. and replacing it with comma in my module.

if my text file contains any "update" statement or "insert" statement it is working good. if my text file has any if statement or "select" statement. it is not working. can u please say why it is not working?

if i try to run the following statement, then also it is not working.
Quote: strFinalString = "if 4=4 then insert into student(sname) values(Robert)"
DBEngine(0)(0).Execute strFinalString Thanks in advance...

I want to set the combo box's LimitToList property to Yes, and be able to
enter an * (asterix) so that the user can only select the numeric ID from the
list or can enter the * wild card (which is text) to get all records.

Hi all I have an unbound combo box named 'txtSSDetailsID' in an unbound form
'aStaffTrainingDeleteMainF'.

This field allows me to select the id number from the aStaffSiteDetailsQ
Query and provides additional information to the user to help them to select
the correct record. Note that I have other fields on this form as well that
when combined allow the query to pull out specific information.

The select statement for the 'txtSSDetailsID' is as follows:

SELECT [aStaffSiteDetailsQ].[ID], [aStaffSiteDetailsQ].[StaffID],
[aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[StaffLastName],
[aStaffSiteDetailsQ].[SiteCode], [aStaffSiteDetailsQ].[Site],
[aStaffSiteDetailsQ].[RoleTitle], [aStaffSiteDetailsQ].[StaffActInact] FROM
aStaffSiteDetailsQ ORDER BY [aStaffSiteDetailsQ].[StaffLastName],
[aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[Site];

The 'aStaffSiteDetailsQ' ID is an automatically generated numeric number.

The idea is that the user opens the 'aStaffTrainingDeleteMainF' form and
selects a record from the 'txtSSDetailsID' (and other fields). The ID
selected is then used by the 'aStaffSiteDetailsQ' query.

I believe that if I use the NotInList event that any entry made will be added
to my table. I do not want this to happen.

I want to set the combo box's LimitToList property to Yes, and be able to
enter an * (asterix) so that the user can only select the numeric ID from the
list or can enter the * wild card (which is text) to get all records.

Can anyone help me here?

Hi,
I am trying to do something that seems fairly easy, but I’m having some difficultly and hopefully someone can help. Here is a high level over of what I’m trying to do:

I have two tables, Table 1 named OLD and Table 2 named NEW. Table 1 is a small group of records and Table 2 has a large quantity of records. They are both very similar in their content where they both contain fields for a PART NUMBER, MANUFACTURER’S NAME, PIECE PRICE, etc..

My goal is to find all records in Table 2 whose values in the PART NUMBER field (PN) match or “closely match” the PART NUMBER (PN) value in Table 1. In most cases where the two records are actually a match, the PN data from the two tables won’t match exactly. So to find records in Table 2 that “closely match” Table I did the following using standard queries.

* I created 2 forms;
- Frm1 uses all the data from Table 1 (OLD)
- Frm2 uses all the data from Table 2 (NEW)
* First I open Frm1.
* The query behind Frm1 has the fields from Table 1, but it also has an expression named MidPN that is the mid$ of a field in the table, PN. In my example I’m using Mid$([PN],3,10). I can’t have 3 and 10 hard coded, more on that later.
* I have placed MidPN on Frm1
* While in Frm1 Form I click on a button to open up Frm2
* In Frm2's query I have a condition that looks at the mid$ expression from Frm1, MidPN, and only opens records in Frm2 that match
* The unique aspect of this is that I add wild cards to either side of the mid$ expression so it will find all "like" matches (Like "*"+[forms]![Frm1]![MidPN]+"*")
Using standard queries this works perfectly. I can scroll through Frm1, hit the "open Frm2" button and the list of matching records comes up. It works Great.


WHAT I WANT TO DO THUS WHY I NEED TO OPEN THE FORM IN VBA.

1) In Frm1 I'd like the mid$ variables to be user defined. Ideally they would set them before opening the Frm1 (say 3 characters in and length of 10). Then, as the user scrolls through Frm1 they could change these two values at any time before opening Frm2. This would allow them to either expand or narrow their search based on the mid$ values. When they went to the next record in Frm1 the values would revert to the initial setting (in this case 3 and 10). But again, if they needed to they could change before once again opening Frm2.

2) When they open Frm2 I'd like to keep Frm1 open and visible. They need both forms open because they must determine if the two records are actually a match. This is best done if they can view both sets of data. While both forms are open, if they determine there is a match, they need to be able to click on a check box in Frm2. Is it possible to tile forms horizontally and go back and forth between the two forms?

All this could be solved if there was a one to many link that could use the “like” expression, but I don’t think this is possible.



THIS IS SQL STATEMENT DIRECTLY FROM QUERY BEHIND FRM1:
SELECT Mid$([PN],1,8) AS MidPN, OLD.PN, OLD.Manufacturer, OLD.Description, OLD.Value, OLD.Designator, OLD.Quantity, OLD.Piece_Price, OLD.Vendor, OLD.Notes
FROM OLD;

THIS IS SQL STATEMENT DIRECTLY FROM QUERY BEHIND FRM2:
SELECT NEW.match, NEW.PN, NEW.Manufacturer, NEW.Description, NEW.Value, NEW.Designator, NEW.Quantity, NEW.Piece_Price, NEW.Vendor, NEW.Notes
FROM NEW
WHERE (((NEW.Value) Like "*"+[forms]![Frm1]![MidPN]+"*"));


BELOW IS MY VBA CODE WHERE I TRY TO DUPLICATE WHAT ACCURS IN THE QUERIES:
The key is the “WHERE” statement. In my example below it doesn’t try and use the “wild cards” with the mid$ statement because I couldn’t get it working. That is where I need help because I’ve tried a ton of different syntaxes, but continue to get errors.

Dim strSQL As String
'
strSQL = "SELECT New.[PN], New.[Manufacturer], New.[Value], New.[Description], New.[Designator], New.[Quantity], New.[Vendor], New.[Notes]" & _
"FROM New " & _
"WHERE New.[PN] ='" & [Forms]![Frm1]![MidPN] & "' ; "
'
Set rs = CurrentDb.OpenRecordset(strSQL)
DoCmd.OpenForm "frm2", , , , , acNormal
Forms!frm2.RecordSource = strSQL
Forms!frm2.Visible = True
'
End Sub


So, to recap my questions:
* How can I code the WHERE statement so it works like the wild card configuration in the standard query?

* How can I have the mid$ values set up as a variable so the user can change?

* Can I have both Frm1 and Frm2 open at the same time and can the user edit them?

Thanks in advance for your help. If I can get this working it will help me tremendously.

I currently have a report linked to this table. The sole function of this table is to association a two letter code with a program name. When the report runs, it prompts the Query to ask first:

Program Name
Warehouse
Month

If nothing is selected the query by default will elect a wildcard search, resulting in everything you don’t specifically ask for.

All of this will feed into a report. In the report I have 2 text boxes – one is linked to a separate table which references the months of the year, joined to my master table so when a month is chosen, the text box auto-populates with the letter month.The other text box is for the program names.

My question is – If a wildcard is elected by not entering anything, can I create an entry on the joined tables for “ALL AGENCIES”, “ALL MONTHS”, or “ALL PROGRAMS” that will be selected by default if the wild card is chosen?

I have tried leaving the table entry blank, "*". Is there maybe an expression i can build? or some SQL language i can add?. . . so far nothing works - but then again I am quite the noob . . . Thanks for posting!

Semper Fi
Dave

Hi Guys

could anyone please help me how to store the result of a select statement in a variable. e.g

	Code:
	a="select count(*) from tblmain where option='Internal Mail' "

I hope anyone can help me in this.

Regards
Aman

I’m trying to run two Select statements from one event (a button on a form without fields) where the “WHERE” criteria are different.

If I was doing this from a form where I could use the value of a field to do an "ELSE…Then…End If" statement, it would be no problem, but basically I’m trying to have this loop through the first Select statement where certain criteria are true and then move to the next Select statement where other criteria are true (the values not true in the first Select statement are true in the second Select statement). I need it to run this way as the verbiage in the e-mails is different based on the Select statement criteria.

I highlighted in red (about half way down) where I think something should go, but I can't for the life of me figure out what it is.

Here’s me raising the white flag to access and humbly asking for some gentle guidance. Otherwise, the rest of the code is good and the two Select statements work when I run them from their own button. I could have more than one button, but what’s the point of doing that if I can combine the two select statements into one event.

Just need a little nudge on this one…




Private Sub Send2DaySTARTNotifications_Click()

Dim response As Integer
response = MsgBox(" Are you sure you want to send e-mails for incomplete START requests?", vbYesNo, "2-day START Request Follow Up")
If response = vbNo Then
MsgBox ("Email Notifications Not Sent.")

Else

DoCmd.SetWarnings False

Dim rs As DAO.Recordset
Dim sBody As String
Dim sSubject As String
Dim sTo As String
Dim sCC As String

Dim sSQL As String
Me.Refresh

'Set rs = CurrentDb.OpenRecordset("select * from 1STARTRequestLog", dbOpenDynaset, dbSQLPassThrough)
sSQL = " SELECT [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS [FullName], [1STARTRequestLog].[LastName] & ', ' & [1STARTRequestLog].[FirstName] & ' - ' & [1STARTRequestLog].System & ' - ' & Date() AS [START Subject], [1STARTRequestLog].HireType, [1STARTRequestLog].Ministry, [1STARTRequestLog].DeptName, [1STARTRequestLog].Manager, [1STARTRequestLog].[Cost Center], [PL] & ' - ' & [Dept] AS [PL-Dept], [1STARTRequestLog].JobTitle, [1STARTRequestLog].PositionCode, [1STARTRequestLog].EEID, [1STARTRequestLog].ApplicantID, [1STARTRequestLog].Req, [1STARTRequestLog].System, [1STARTRequestLog].PermissionLevel, [1STARTRequestLog].DateRequestCompleted, [1STARTRequestLog].DateTimeEmail2, [1STARTRequestLog].ToSysAdminEmail, [1STARTRequestLog].CCSysAdminEmail1, [1STARTRequestLog].TASORepEmail, [1STARTRequestLog].DateEmail1, [1STARTRequestLog].ProposedStartDate, [1STARTRequestLog].ToSysAdminName" & _
" From 1STARTRequestLog" & _
" WHERE ((([1STARTRequestLog].FirstName)= 'Test') AND (([1STARTRequestLog].HireType) = 'Current PH&S CA Region Employee' Or ([1STARTRequestLog].HireType) = 'PH&S Employee Inter-region Transfer' Or ([1STARTRequestLog].HireType) = 'Traveler Converting to PH&S CA Region Employee') AND (([1STARTRequestLog].DateRequestCompleted) Is Null) AND (([1STARTRequestLog].DateTimeEmail2) Is Null));" _

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSQLPassThrough)


If rs.RecordCount > 0 Then
Do While Not rs.EOF

sSubject = "TEST (Effective Date) Follow Up START Notification: " & rs("[START Subject]")
sBody = "Dear " & rs("[ToSysAdminName]") & "," & vbNewLine & vbNewLine
sBody = sBody & "The START request sent " & rs("[DateEmail1]") & " has not yet been completed. This is a follow up notification, reminding you to complete the START request as determined by the department manager. Once you have completed this request, please conserve the details of this e-mail thread by FORWARDING this mail to " & rs("[TASORepEmail]") & " with any applicable username and password logon information for HR to close this START request." & vbNewLine & vbNewLine
sBody = sBody & "Name: " & rs("[FullName]") & vbNewLine
sBody = sBody & "Hire Type: " & rs("[HireType]") & vbNewLine
sBody = sBody & "Ministry: " & rs("[Ministry]") & vbNewLine
sBody = sBody & "Department Name: " & rs("[DeptName]") & vbNewLine
sBody = sBody & "Manager: " & rs("[Manager]") & vbNewLine
sBody = sBody & "Cost Center: " & rs("[Cost Center]") & vbNewLine
sBody = sBody & "PL - Dept: " & rs("[PL-Dept]") & vbNewLine
sBody = sBody & "Job Title: " & rs("[JobTitle]") & vbNewLine
sBody = sBody & "Position Code: " & rs("[PositionCode]") & vbNewLine
sBody = sBody & "Employee ID: " & rs("[EEID]") & vbNewLine
sBody = sBody & "Applicant ID: " & rs("[ApplicantID]") & vbNewLine
sBody = sBody & "Req: " & rs("[Req]") & vbNewLine
sBody = sBody & "System: " & rs("[System]") & vbNewLine
sBody = sBody & "Permission Level: " & rs("[PermissionLevel]") & vbNewLine
sBody = sBody & "Effective Date of Transfer: " & rs("[ProposedStartDate]") & vbNewLine & vbNewLine

sBody = sBody & "NOTE: Please conserve the details of this e-mail thread by FORWARDING this e-mail to " & rs("[TASORepEmail]") & " once this request is complete with any applicable password and username information. Human Resources provides this information to the transferring employee prior to the transfer effective date of " & rs("[ProposedStartDate]") & "." & vbNewLine & vbNewLine
sBody = sBody & "If you are not the administrator for this system, please forward this request to the appropriate system administrator or contact the START system administrator at start@providence.org. A follow up e-mail will be sent in 2 days to escalate this request if system access is incomplete." & vbNewLine & vbNewLine
sBody = sBody & "Thank you," & vbNewLine & "START Administrator"
sTo = rs("[ToSysAdminEmail]")
If rs("[CCSysAdminEmail1]") = Null Or Len(rs("[CCSysAdminEmail1]")) > 0 Then
sCC = rs("[CCSysAdminEmail1]")
End If

If sTo "" Then
SendEmail sTo, sCC, "", sSubject, sBody
End If
rs.MoveNext
Loop

Else
'msgbox "No records found."
End If '' Recordcount > 0

'SEEMS LIKE I NEED SOMETHING HERE TO LOOK AT THE WHERE STATEMENT BELOW IF THE SELECT WHERE STATEMENT ABOVE IS NOT TRUE

Dim rs1 As DAO.Recordset
Dim sBody1 As String
Dim sSubject1 As String
Dim sTo1 As String
Dim sCC1 As String

Dim sSQL1 As String
Me.Refresh

'Set rs = CurrentDb.OpenRecordset("select * from 1STARTRequestLog", dbOpenDynaset, dbSQLPassThrough)
sSQL1 = " SELECT [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS [FullName], [1STARTRequestLog].[LastName] & ', ' & [1STARTRequestLog].[FirstName] & ' - ' & [1STARTRequestLog].System & ' - ' & Date() AS [START Subject], [1STARTRequestLog].HireType, [1STARTRequestLog].Ministry, [1STARTRequestLog].DeptName, [1STARTRequestLog].Manager, [1STARTRequestLog].[Cost Center], [PL] & ' - ' & [Dept] AS [PL-Dept], [1STARTRequestLog].JobTitle, [1STARTRequestLog].PositionCode, [1STARTRequestLog].EEID, [1STARTRequestLog].ApplicantID, [1STARTRequestLog].Req, [1STARTRequestLog].System, [1STARTRequestLog].PermissionLevel, [1STARTRequestLog].DateRequestCompleted, [1STARTRequestLog].DateTimeEmail2, [1STARTRequestLog].ToSysAdminEmail, [1STARTRequestLog].CCSysAdminEmail1, [1STARTRequestLog].TASORepEmail, [1STARTRequestLog].DateEmail1, [1STARTRequestLog].ProposedStartDate, [1STARTRequestLog].ToSysAdminName" & _
" From 1STARTRequestLog" & _
" WHERE ((([1STARTRequestLog].FirstName)= 'Test') AND (([1STARTRequestLog].HireType) = 'Standard New Hire' Or ([1STARTRequestLog].HireType) = 'Reinstated, Former Providence Employee' Or ([1STARTRequestLog].HireType) = 'Rehired, Former Providence Employee') AND (([1STARTRequestLog].DateRequestCompleted) Is Null) AND (([1STARTRequestLog].DateTimeEmail2) Is Null));" _

Set rs1 = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSQLPassThrough)


If rs1.RecordCount > 0 Then
Do While Not rs.EOF

sSubject1 = "TEST (No Effective Date) Follow Up START Notification: " & rs1("[START Subject]")
sBody1 = "Dear " & rs1("[ToSysAdminName]") & "," & vbNewLine & vbNewLine
sBody1 = sBody1 & "The START request sent " & rs1("[DateEmail1]") & " has not yet been completed, this is a follow up notification, reminding you to complete the following START request as determined by the department manager. If you have completed this request, please FORWARD this mail to " & rs1("[TASORepEmail]") & " with any applicable username and password logon information for HR to close this START request." & vbNewLine & vbNewLine
sBody1 = sBody1 & "Name: " & rs1("[FullName]") & vbNewLine
sBody1 = sBody1 & "Hire Type: " & rs1("[HireType]") & vbNewLine
sBody1 = sBody1 & "Ministry: " & rs1("[Ministry]") & vbNewLine
sBody1 = sBody1 & "Department Name: " & rs1("[DeptName]") & vbNewLine
sBody1 = sBody1 & "Manager: " & rs1("[Manager]") & vbNewLine
sBody1 = sBody1 & "Cost Center: " & rs1("[Cost Center]") & vbNewLine
sBody1 = sBody1 & "PL - Dept: " & rs1("[PL-Dept]") & vbNewLine
sBody1 = sBody1 & "Job Title: " & rs1("[JobTitle]") & vbNewLine
sBody1 = sBody1 & "Position Code: " & rs1("[PositionCode]") & vbNewLine
sBody1 = sBody1 & "Employee ID: " & rs1("[EEID]") & vbNewLine
sBody1 = sBody1 & "Applicant ID: " & rs1("[ApplicantID]") & vbNewLine
sBody1 = sBody1 & "Req: " & rs1("[Req]") & vbNewLine
sBody1 = sBody1 & "System: " & rs1("[System]") & vbNewLine
sBody1 = sBody1 & "Permission Level: " & rs1("[PermissionLevel]") & vbNewLine & vbNewLine

sBody1 = sBody1 & "NOTE: Please conserve the details of this e-mail thread by FORWARDING this e-mail to " & rs1("[TASORepEmail]") & " once this request is complete with any applicable password and username information. Human Resources provides this information to the new employee prior to his/her hire date." & vbNewLine & vbNewLine
sBody1 = sBody1 & "If you are not the administrator for this system, please forward this request to the appropriate system administrator or contact the START system administrator at start@providence.org. A follow up e-mail will be sent in 24 hours to further escalate this request if system access is incomplete." & vbNewLine & vbNewLine
sBody1 = sBody1 & "Thank you," & vbNewLine & "START Administrator"
sTo1 = rs1("[ToSysAdminEmail]")
If rs1("[CCSysAdminEmail1]") = Null Or Len(rs1("[CCSysAdminEmail1]")) > 0 Then
sCC1 = rs1("[CCSysAdminEmail1]")
End If

If sTo1 "" Then
SendEmail sTo1, sCC1, "", sSubject1, sBody1
End If
rs1.MoveNext
Loop

Else
'msgbox "No records found."
End If '' Recordcount > 0


MsgBox (" Follow Up START Notifications Successfully Sent for Net Systems")

strSQL = "UPDATE 1STARTRequestLog SET 1STARTRequestLog.DateTimeEmail2 = Date()"
strSQL = strSQL & "WHERE ((([1STARTRequestLog].DateRequestCompleted) Is Null) AND (([1STARTRequestLog].DateTimeEmail2) Is Null) AND (([1STARTRequestLog].DateTimeEmail3) Is Null));"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

End If

End Sub

So I have the following statement:

Quote: SELECT *
FROM ShiftsBusesSC
WHERE (((ShiftsBusesSC.[Day Of Week])=TempVars!Day) And ((ShiftsBusesSC.Category) In (TempVars!X1,TempVars!X2)) And ((ShiftsBusesSC.[Employee Last Name]) Is Null Or (ShiftsBusesSC.[Employee Last Name])"ACCOUNT PENDING")); Those TempVars (in bold) are defined using the following code before being referenced, and there is no issue with anything. It all works fine.

Quote: TempVars.Add "X1", "E"
TempVars.Add "X2", "ETR" However, I also have this statement:

Quote: SELECT ShiftChangeX.[Employee Number], ShiftChangeX.Category, ShiftChangeX.[Shift Description], ShiftChangeX.Date, ShiftChangeX.[Start Time], ShiftChangeX.[End Time], ShiftChangeX.Duration, ShiftChangeX.[Day Of Week], ShiftChangeX.[Employee First Name], ShiftChangeX.[Employee Last Name], ShiftChangeX.Route, ShiftChangeX.Bus FROM ShiftChangeX LEFT JOIN ShiftChangeY ON ShiftChangeX.[Employee Number] = ShiftChangeY.[Employee Number] WHERE (((ShiftChangeY.[Employee Number]) Is Null) AND (TempVars!X3)) UNION SELECT ShiftChangeY.[Employee Number], ShiftChangeY.Category, ShiftChangeY.[Shift Description], ShiftChangeY.Date, ShiftChangeY.[Start Time], ShiftChangeY.[End Time], ShiftChangeY.Duration, ShiftChangeY.[Day Of Week], ShiftChangeY.[Employee First Name], ShiftChangeY.[Employee Last Name], ShiftChangeY.Route, ShiftChangeY.Bus FROM ShiftChangeY LEFT JOIN ShiftChangeX ON ShiftChangeY.[Employee Number] = ShiftChangeX.[Employee Number] WHERE (((ShiftChangeX.[Employee Number]) Is Null) AND (TempVars!Y3)); In this case, those two TempVars are defined using the following piece of code (X3 in this example):

Quote: TempVars.Add "X3", "(ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*12E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*21E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*32E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*40E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*50E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*80E*" & Chr(34) I realize this is slightly more complex, but I know that the resulting value of the TempVar is EXACTLY what would normally be in the select statement. Every single character is identical, and it worked before. However, in this case, the resulting report seems to ignore this part. You can see here by the "not like" statements that I am attempting to exclude a few records from the result, but they are not excluded, so it makes me think I'm doing something wrong that causes Access to gloss over this last part. I get no errors, but it simply doesn't seem to do anything with the TempVar at all. Does anyone know why this works fine in my first example, but not in my second?

The problem that i am having is that i am trying to convert convetional access queires to code because they involve using a few "IIf" statements in each and they are so slow i might die before they finish running!

Using conventional queires it is just about impossible to extract and manipulate the data using a single query, so i have a main query that gets the "raw" data, and a second query based on the first which sums the results etc.
(i have to do this for 3 sets of data on the same form, which is 6 queries in total).

What i am trying to do is create a recordset conatining all the data that i need, then work through it summing the data etc, displaying the results in a list box with option buttons to display the results.

This is all fine so far, but the bit that has me stumped is this.

I have created the first select statement which creates the main recordset in memory, BUT basing the second SELECT statement on this recordset results in access telling me that it can't find the named query/table?

There has got to be a way of creating a recordset and then using a SELECT statement to extract the relevant data from this recordset?

As a side issue, i'm pretty sure i'm going to need to "tag" a couple of fields onto this recordset, is there an easy way of adding a couple of field names to a recordset created in memory, and do i do this before or after the recordset is created?

I haven't got much hair left as it is, so if anyone can help both me and my head will be very greatful!

Thanks
Vince


SAMPLES OF CODE BEING USED SO FAR
-----------------------------------------------------------------------------------
Query1 pulls the main data from the table

SELECT tblSOP.AccountOwner AS EmployeeID, tblSOP.CommissionPaid, (IIf(Format([CommissionPaid])"","yes","No")) AS Paid, tblSOP.Status, tblSOP.DateOrder, tblSOP.OrderID, tblSOP.CompanyID, tblCompany.CompanyName, tblSopItem.ItemNumber AS SOPItem, tblSopItem.Deleted, tblSopItem.ProductID, tblSopItem.ProductDescription, tblPurchasingDespatch.DespatchID, tblPurchasingHistory.PurchasingID, tblSopItem.Quantity AS QtySold, tblSopItem.PriceEach AS SOPPrice, tblSopItem.CostEach AS SysCost, tblPurchasingHistory.ActualQtyBought AS ActBought, tblPurchasingHistory.ActualCostEach AS ActCost, tblPurchasingDespatch.QtyDespatched, tblPurchasingHistory.POPOrderId, tblPOPItem.ActualCost, ([QtyDespatched]*[ActualCost]) AS SOPCost, [qtydespatched]*[sopprice] AS [Value], [qtydespatched]*[actualcost] AS Cost, [Value]-[Cost] AS Profit, Format((([Profit])*(DLookUp("Value","tblValue","Item = '" & 35 & "'")/100)),"Fixed") AS Commission
FROM (tblPurchasingHistory INNER JOIN tblPOPItem ON (tblPurchasingHistory.POPOrderId = tblPOPItem.OrderID) AND (tblPurchasingHistory.POPItemNumber = tblPOPItem.ItemNumber)) INNER JOIN ((tblCompany INNER JOIN tblSOP ON tblCompany.CompanyID = tblSOP.CompanyID) INNER JOIN (tblPurchasingDespatch INNER JOIN tblSopItem ON (tblPurchasingDespatch.SOPItemNumber = tblSopItem.ItemNumber) AND (tblPurchasingDespatch.SOPOrderID = tblSopItem.OrderID)) ON tblSOP.OrderID = tblSopItem.OrderID) ON tblPurchasingHistory.PurchasingID = tblPurchasingDespatch.PurchasingID
WHERE (((tblSOP.AccountOwner)=[Forms]![frmCommissionList]![cboEmployee]) AND (((IIf(Format([CommissionPaid])"","yes","No")))=[Forms]![FrmCommissionList]![cboPaid]) AND ((tblSOP.Status)="complete") AND ((tblSopItem.Deleted)=False));

------------------------------------------------------------------------------------
Query2 is then based on query 1 to sum and group the data

SELECT QryCommissionCompletesDetail.EmployeeID AS Emp, QryCommissionCompletesDetail.Status, Format([DateOrder],"mmmyyyy") AS [Date], QryCommissionCompletesDetail.OrderID, IIf(DFirst("OrderID","QryCommissionOrders","Compan yID = '" & [CompanyID] & "'")=[OrderID] And (Sum([Profit]))>25 And Format(Sum(([Profit]))/Sum([Value])*100,"Fixed")>25,"Yes","") AS NewAcc, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, Count(QryCommissionCompletesDetail.SOPItem) AS Lines, Sum(QryCommissionCompletesDetail.Value) AS Turnover, Sum(QryCommissionCompletesDetail.Commission) AS Commission, Format(Sum(([Profit]))/Sum([Value])*100,"Fixed") AS Margin, IIf([NewAcc]="Yes",([Commission]*2),[Commission]) AS Pay, QryCommissionCompletesDetail.DateOrder
FROM QryCommissionCompletesDetail
GROUP BY QryCommissionCompletesDetail.EmployeeID, QryCommissionCompletesDetail.Status, Format([DateOrder],"mmmyyyy"), QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, QryCommissionCompletesDetail.DateOrder
HAVING (((QryCommissionCompletesDetail.EmployeeID)=[Forms]![frmCommissionList]![cboEmployee]) AND ((Format([DateOrder],"mmmyyyy"))=[Forms]![frmCommissionList]![cboMonth] & [Forms]![frmCommissionList]![cboYear]))
ORDER BY QryCommissionCompletesDetail.DateOrder DESC;


------------------------------------------------------------------------------------


SQL string built behind form so far, gets the same results as query one, but I can’t base the second SQL string on the resultant recordset "rstData"

Private Sub cmdCompletesDetail_Click()
Dim cDetail As String
Dim cSummary As String
Dim rstData As DAO.Recordset

'Build the detailed recordset to get the bulk data from the tables...
cDetail = "SELECT tblSOP.EmployeeID, tblSOP.AccountOwner, tblSOP.CommissionPaid, tblSOP.Status, tblSOP.DateOrder, tblSOP.OrderID, tblSOP.CompanyID, tblCompany.CompanyName, tblSopItem.ItemNumber, tblSopItem.Deleted, tblSopItem.ProductID, tblSopItem.ProductDescription, tblPurchasingDespatch.DespatchID, tblPurchasingHistory.PurchasingID, tblSopItem.Quantity, tblSopItem.PriceEach, tblSopItem.CostEach, tblPurchasingDespatch.QtyDespatched, tblPurchasingHistory.POPOrderId,tblPOPItem.ActualC ost "
cDetail = cDetail & "FROM (tblCompany INNER JOIN tblSOP ON tblCompany.CompanyID = tblSOP.CompanyID) INNER JOIN ((tblPurchasingHistory INNER JOIN tblPOPItem ON (tblPurchasingHistory.POPItemNumber = tblPOPItem.ItemNumber) AND (tblPurchasingHistory.POPOrderId = tblPOPItem.OrderID)) INNER JOIN (tblPurchasingDespatch INNER JOIN tblSopItem ON (tblPurchasingDespatch.SOPOrderID = tblSopItem.OrderID) AND (tblPurchasingDespatch.SOPItemNumber = tblSopItem.ItemNumber)) ON tblPurchasingHistory.PurchasingID = tblPurchasingDespatch.PurchasingID) ON tblSOP.OrderID = tblSopItem.OrderID "
cDetail = cDetail & "WHERE (((Format(tblSOP.DateOrder, 'mmmyyyy'))= '" & Me.CboMonth & Me.CboYear & "') AND (((tblSOP.EmployeeID)) = '" & Me.cboEmployee & "') AND (((tblSOP.Status)) = '" & "Complete" & "')) OR (((Format(tblSOP.DateOrder, 'mmmyyyy')) = '" & Me.CboMonth & Me.CboYear & "') AND (((tblSOP.AccountOwner)) = '" & Me.cboEmployee & "') AND (((tblSOP.Status)) = '" & "Complete" & "') AND (((tblSOP.Deleted)) = False))ORDER BY tblSOP.OrderID DESC;"

Set rstData = CurrentDb.OpenRecordset(cSOPCompletes, dbOpenSnapshot)
If rstData.RecordCount > 0 Then
rstData.MoveFirst

'Second SELECT statement goes here selecting records FROM rstData

End If

'Me.LstCompletes.RowSource = cDetail (works fine so far with list box displaying the first recordset)

Me.LstCompletes.rowsource = cSummary
Me.LstCompletes.Requery
Me.txtOrderCount = Me.LstCompletes.ListCount

End Sub

I am trying to conditionally select information from a table to create input for a Merge-Mail document. The output is an award certificate. Sometimes I want to insert some text at a specific location and sometimes I want to insert nothing, depending on a variable/field selected from another table (in the same SELECT statement).

Here is the select statement (before my "fix" that didn't work):

strSelect = "SELECT tblDogTitles.dogtitleID, tblDogTitles.dogregnbr, tblDogTitles.processeddt, Day(tblTrials.trialdt) & ' ' & MonthName(Month(tblTrials.trialdt)) & ' ' & Year(tblTrials.trialdt) AS outtrialdt, tblTitles.title, tblTitles.titleabbrev, tblDogs.formalname, tblPeople.fname & (' '+tblPeople.midinit+'.') & ' ' & tblPeople.lname & (' '+tblPeople.suffix+'.') AS owner, tblPeople.altperson AS altowner, tblClasses.class & ' Class' AS classtxt, tblUserSettings.uval FROM tblUserSettings, tblTrials INNER JOIN (((tblClasses INNER JOIN tblTrialClass ON tblClasses.classID = tblTrialClass.classID) INNER JOIN tblTitles ON tblClasses.classID = tblTitles.classID) INNER JOIN (tblPeople INNER JOIN (tblDogs INNER JOIN tblDogTitles ON tblDogs.dogregnbr = tblDogTitles.dogregnbr) ON tblPeople.peopleID = tblDogs.peopleID) ON (tblTrialClass.trialclassID = tblDogTitles.trialclassID) AND (tblTitles.titleID = tblDogTitles.titleID)) ON tblTrials.trialID = tblTrialClass.trialID "

Here is my attempt at a fix, which didn't work:

strSELECT = "SELECT tblDogTitles.dogtitleID, tblDogTitles.dogregnbr, tblDogTitles.processeddt, Day(tblTrials.trialdt) & ' ' & MonthName(Month(tblTrials.trialdt)) & ' ' & Year(tblTrials.trialdt) AS outtrialdt, tblTitles.title, tblTitles.titleabbrev, tblDogs.formalname, tblPeople.fname & (' '+tblPeople.midinit+'.') & ' ' & tblPeople.lname & (' '+tblPeople.suffix+'.') AS owner, tblPeople.altperson AS altowner, IIF(titleabbrev = 'UDX-H' OR titleabbrev = 'OTCH-H','' AS classtxt,tblClasses.class & ' Class' AS classtxt), tblUserSettings.uval FROM tblUserSettings, tblTrials INNER JOIN (((tblClasses INNER JOIN tblTrialClass ON tblClasses.classID = tblTrialClass.classID) INNER JOIN tblTitles ON tblClasses.classID = tblTitles.classID) INNER JOIN (tblPeople INNER JOIN (tblDogs INNER JOIN tblDogTitles ON tblDogs.dogregnbr = tblDogTitles.dogregnbr) ON tblPeople.peopleID = tblDogs.peopleID) ON (tblTrialClass.trialclassID = tblDogTitles.trialclassID) AND (tblTitles.titleID = tblDogTitles.titleID)) ON tblTrials.trialID = tblTrialClass.trialID "

Any ideas on how to get what I want?

Regards,
SueB

Hi,

I have a problem just executing a select statement. I want the value of a textbox to be determined by a simple select query.

I tried to use the Expression Builder but I don't know how to put arguments when using queries in it. For example, you want to select a field 'algemeenid' based on another field 'woningid'. If a query for this, named "selectionquery". Can anybody tell me how to do this?

Secondly, I tried to implement it as a visual basic function. I tried the following code, but it gives an error, and I don't know how to correct it.

This is the code

	Code:
	Private Sub Text25_Click()

Dim sql String
Dim rec As Recordset

sql = "SELECT algemeenid FROM woning WHERE woningid = 2;"
rec = DoCmd.OpenQuery(sql)

Text25 = rec

The error I get is a compile error "Expected Function or Variable". Could anyone please help? Any help on the syntax would be great.

I am trying to use the variable strTest as a variable in the select statement below.

The fields in the custody database contain F8 thru F34.

Private Sub txtMethod_BeforeUpdate(Cancel As Integer)
strA = 8
Do While strA < 35
strTest = "F" + strA
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select strTest from Custody where LABID = '" & Me.txtLabID & "' AND strTest = '" & Me.txtMethod & "';")
strA = strA + 1
Loop

If rst.RecordCount = 0 Then
MsgBox Me.txtMethod + " is not required for Lab ID: " + Me.txtLabID
End
End If

End Sub




If I substitute this line of code:

Set rst = dbs.OpenRecordset("Select F8 from Custody where LABID = '" & Me.txtLabID & "' AND F8 = '" & Me.txtMethod & "';")


the line works just fine.

Obviously I would like to avoid repeating the line 26 times changing F8 to F9 to F10......


I also tried to use an if then statement in a loop with a counter.

The bottom line: I have not been able to reference the variable to see the contents of fields F8 - F34

As always, any help is greatly appreciated.

Thanks,

B

Hi Guys,
Can you help please? I have entered the following SQL statement in my sub and I got the following error:
Quote: The SELECT statement includes a reserved word or argument name that is missspelled or missing, or the punctuation is incorrect Where have I gone wrong?


	Code:
	Dim MySQL As String
MySQL = "SELECT XRayedConsignments.[EntryDate/Time], XRayedConsignments.EntryTime, XRayedConsignments.AWBNumber,
XRayedConsignments.Pieces, XRayedConsignments.Weight, XRayedConsignments.OpsIDNumber, XRayedConsignments.Mail,
XRayedConsignments.Transhipment, XRayedConsignments.Iberia, XRayedConsignments.UnitedAirlines, XRayedConsignments.ELAL,
XRayedConsignments.AirMauritus, FROM XRayedConsignments"
MyRecordset.Open MySQL

Thanks very much


Not finding an answer? Try a Google search.