Works as a query but not in vba? (1 Viewer)

[mdray00]

Hi Guys,

Im trying to loop through a query results to extract email addresses from a query result.

I have the following code

NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value

DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value


Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim sqlStr As String
sqlStr = "SELECT tbl_Courses.[E-Mail Address] " & _

"FROM tbl_Course_Details INNER JOIN tbl_Courses ON (tbl_Course_Details.[Course Name] = tbl_Courses.[Course Name]) AND (tbl_Course_Details.[Course Date] = tbl_Courses.Date) " & _

"WHERE (((tbl_Course_Details.[Course Name])="" & NameCriteria & "") AND ((tbl_Course_Details.[Course Date])=#" & DateCriteria & "#));"

Set db = CurrentDb

Set rs = db.OpenRecordset(sqlStr)

rs.MoveFirst

Do While Not rs.EOF

MsgBox "User Email = " & rs![E-Mail Address]

rs.MoveNext

Loop



If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?

Someone on another post said that sql uses US date format so i tried converting to us date and still no results?

Thanks in advance.

 

[DavidAtWork]

If it's the date causing the problem, then I'd try this:
tbl_Course_Details.[Course Date])=#" & format(DateCriteria, "mm/dd/yyyy") & "#

Also presumably you have declared variables for NameCriteria and DateCriteria,
if the nameCriteria is a string, the syntax may need to change to:
=' " & NameCriteria & " ' or """ & NameCriteria & """
An easy way to test if the syntax around the variable has been recognised is to change the capital N of NameCriteria to a small n and it if jumps back to capital N, it should be ok

David

 

[mdray00]

I have noticed when i looked more that it works in american format but different?

The date for example if 01/08/2013 in uk format when converted to US it is 08/01/2013 but the date that makes the query work is 8/1/2013 how do i convert 01/08/2013 in to 8/1/2013 as i used the mm/dd/yyyy method but that gives me 08/01/2013 and what happens if the date was say 8/22/2013?

Sorry and thanks

 

[JHB]

One thing that is wrong is this:

tbl_Course_Details.[Course Name])="" & NameCriteria & "") ...

Text criteria has to be surrounded by a single quarto.

tbl_Course_Details.[Course Name])=" & "'" & NameCriteria & "'" & ") ...

 

[mdray00]

Thats great works like a charm now thanks alot David

 

[missinglinq]

...how do i convert 01/08/2013 in to 8/1/2013 as i used the mm/dd/yyyy method but that gives me 08/01/2013 and what happens if the date was say 8/22/2013...

Anyone who works with non-US formatted Dates needs to have Allen Browne's article on International Dates bookmarked; they'll need it sooner or later:

http://allenbrowne.com/ser-36.html

Linq ;0)>

 

[nanscombe]

I always tended to convert dates on both sides to a common, known, format.

"WHERE (((tbl_Course_Details.[Course Name])="" & NameCriteria & "")
AND ((Format(tbl_Course_Details.[Course Date]),""yyyymmdd"")
='" & Format(DateCriteria,"yyyymmdd") & "'));"

(carriage returns added for clarity)