Syntax Error (comma) in query (1 Viewer)

[ansentry]

Would someone be kind enough to tell me what I have done wrong in the code show below?

Access is give me error 3075 Syntax Error (Comma) in query and is highlighting the last line in my code.


DoCmd.Openform stDocName, , , stLinkCriteria


What I am trying to do is open a form with only the data from the previous day showing. I know I could do this in the query attached to the form, however when I (we) solve this problem, then I want to make the code open Friday, Saturday and Sunday if the current date is a Monday. The business is open 7 days a week but management will only look at the form Monday - Friday to see the previous day's deliveries.

A Command button on a "Menu form" is opening the Form.

Mydate is formatted "Long Date" in its table.


Private Sub cmdPreviousDayDeliveries_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim PrevDate As String

PrevDate = Format(DateValue(Date - 1), "Long Date")

stDocName = "Frm_PrevDayDeliveries"

stLinkCriteria = "MyDate = " & PrevDate



End SubDoCmd.Openform stDocName, , , stLinkCriteria

 

[Dgar007]

You need single quotes around your criteria:

stLinkCriteria = "MyDate = '" & PrevDate & "'"

 

[ansentry]

Dgar007,

Firstly thank you for your quick reply, I have changed it as you can see, I am now getting error 2501. When I go in to "debug" and hold the mouse cursor over the stLinkCriteria in the final line it says 24 June 2003 and the & PrevDate& shows 24 June 2003 and that is correct , so what could be wrong?

Regards





Dim stDocName As String
Dim stLinkCriteria As String

Dim PrevDate As String




PrevDate = Format(DateValue(Date - 1), "Long Date")

stDocName = "Frm_PrevDayDeliveries"


stLinkCriteria = "Mydate = '" & PrevDate & "'"



DoCmd.Openform stDocName, , , stLinkCriteria, , acDialog

 

[Dgar007]

hmmm...is the date value stored in your table as LONG DATE, try removing that portion of your criteria. Many times you use LONG DATE as a format to show on the forms, but is actually stored as 06/25/2003.

 

[ansentry]

Dgar007,

No luck, I changed it but it did not make in difference.

I have check my table and Mydate, format = long date. My query is long date and in the form MYDate, format = long


This is the part of the code that is Highlighted in yellow when I go in to "Debug"


DoCmd.Openform stDocName, , , stLinkCriteria, , acDialog




Thank you in advance for any further help,

Regards

 

[Dgar007]

I recreated the error and when I try to open the query that I am passing the variable to it tells me it is too complicated.

So, I looked at what was going into the argument. I changed the
FORMAT(MyDate, "Short Date") and it worked. Eventhough my data in the table was storing as long date.

 

[Rich]

You have to use the US date format when comparing/returning dates from strings

 

[ansentry]

Dgar007

Sorry now Iam confused, you put FORMAT(MyDate, "Short Date") Where?

Regards

 

[Dgar007]

Thanks Rich, that would explain why Short Date works.

 

[Dgar007]

PrevDate = (Date - 1)
OR
PrevDate = Format(DateValue(Date - 1), "Short Date")

should work

 

[ansentry]

Dgar007


Still no luck, I have attached a sample db of the problem. If you would be kind enough to have a look.

Regards,

 

[WayneRyan]

John,

stLinkCriteria = "[Mydate] = #" & PrevDate & "#"

hth,
Wayne

 

[ansentry]

Wayne,

Thank you very much, it works fine. I tried that but I had stLinkCriteria = "[Mydate] = "# & PrevDate & "#" (second " in wrong place)

Thank you again,


Regards