Recordsets and Queries 1

[rookery]

Can anyone tell me why the code below only works if the argument after CDATE contains ('"&) at the beginning and end, either side of the "true" value? Something to do with strings?

Set MyRs = dbs.OpenRecordset("SELECT * FROM DiaryNotes WHERE DiaryDate = cdate('" & (Me!Calender1) & "')&quot
Me![Text1] = MyRs!DiaryNote

 

[jimmythegeek]

This should work, you have to use pound signs with dates:

Set MyRs = dbs.OpenRecordset("SELECT * FROM DiaryNotes WHERE DiaryDate = #" & (Me!Calender1) & "#"
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[rookery]

Jimmy I could have done with you about 3 days ago!! Although my code works, its good to know of a more efficient procedure.

Thing I still dont understand though is why I have to put ampersands either side of the (Me!Calender) value? Is this somehitng to do with SQL protocol?

Thanks again.

 

[rookery]

Jim

as a footnote i've noticed that the pound signs dont work as efficiently as using the CDate function. eg.

strCriteria = "[DiaryDate]= CDate('" & (Me!Calender1) & "')"

Basically I didnt realise this problem could be resolved by creating a Recordset based on the table and using the FindFirst/Criteria methods. Oh well you learn something new everyday!!

 

[jimmythegeek]

The reason for the ampersands is because you are inserting a dynamic value into a static string. Let me explain:

I want to build a select string, based on a date field on my form. If I built the string like this:

"SELECT * FROM Orders WHERE OrderDate = Me.OrderDate"

It would return this:

SELECT * FROM Orders WHERE OrderDate = Me.OrderDate

This select statement will not work in that the parser will be looking for a VALUE in the WHERE clause, NOT the string "Me.OrderDate"

If I write it like this

"SELECT * FROM Orders WHERE OrderID = #" & Me.OrderDate & "#"

I have in essence created two strings with a value inserted in between them. My first string is this:

"SELECT * FROM Orders WHERE OrderID = #"

My value from my form may be 06/01/2002

And my last string is this:

"#"

The string will first create this:

SELECT * FROM Orders WHERE OrderID = #

Then it will add the value of the Order date on my form (Me.OrderDate):

SELECT * FROM Orders WHERE OrderID = #06/01/2002

Then it will add the last pound sign (#):

SELECT * FROM Orders WHERE OrderID = #06/01/2002#


You are simply inserting a dynamic value into that string using the ampersands (concatenation).

Just remember that if the part of the select statement is a dynamic value, it must be surrounded by ampersands. Any symbols (', #, etc) must be included as part of the string before and after the dynamic value. ONLY the value itself is between the ampersands.

Hopefully this will help. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[rookery]

Jim

Very informative. Thanks for your trouble. I'm sure I'll be calling for your help again somethime in the future!!