Problem Date Format

[ds_8805]

Hello! I have a search form where user can search for details about a site. The site details would be displayed in a sub form. I have a button which would open up a report containing some of the details abt the site. I am facing a small problem here. The form has 2 conditions to make. One is that it should match the siteid that is currently being displayed as well as the startdate displayed in the form should be same as the current date. My code is as below.

Private Sub InstallationFax_Click()
Dim strReportName As String
Dim strCriteria As String
Dim LDate As String
LDate = Format(Date, "dd-mmm-yy")

strReportName = "E1CableFax"
strCriteria = "[SiteID]='" & Me![SiteID] & "'" And "[StartDate]='" & LDate & "'"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
End Sub

Whenever I run this code, I have a type mismatch error. I have checked the formats of both the dates that I am comparing and both are of the this format => dd-mmm-yy' . However, the problem is still there. Hope someone could help me figure out what I might be doing wrong.

Thank You very much

 

[pbaldy]

What are the data types of the two fields? Text and date fields require delimiters, numeric do not:

http://www.baldyweb.com/wherecondition.htm

 

[ds_8805]

SiteID is of text datatype and StartDate is of date/time datatype. what do u mean that they require delimiters?

 

[pbaldy]

Did you review the link?

 

[ds_8805]

hey thanks for ur help. I read the link and changed the code accordingly as such:
Private Sub InstallationFax_Click()
Dim strReportName As String
Dim strCriteria As String
Dim LDate As String
LDate = Format(Date, "dd-mmm-yy")

strReportName = "E1CableFax"
strCriteria = "[SiteID]='" & Me![SiteID] & "'" And "[StartDate]=#" & LDate & "#"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
End Sub

however the same problem still persists.

 

[pbaldy]

You have a couple of extra quotes in there. Try this:

strCriteria = "[SiteID]='" & Me![SiteID] & "' And [StartDate]=#" & LDate & "#"

 

[ds_8805]

Thank You soooooo much! its works perfectly just for learning purposes, if I were to change the condition such that the startdate should not be null, how would I do that?

 

[pbaldy]

Glad it worked for you. If I understand what you want, try:

strCriteria = "[SiteID]='" & Me![SiteID] & "' And [StartDate] Is Not Null"

 

[ds_8805]

yup thks alot for ur help!