Syntax error for filter between dates

R

RoadKyng

Need to filter between two date fields. Have coded two different methods and
get same syntax missing operator error. Have searched postings for clues but
need a little guidance:

stLinkCriteria = "IncidentDate = Between #" & Me.startdate & "# and #" &
Me.enddate & "#"

or

stLinkCriteria = "IncidentDate " & ">=#" & Me.startdate & "# and <=#" &
Me.enddate & "#"

Thanks and have a safe weekend
 
M

Marshall Barton

RoadKyng said:
Need to filter between two date fields. Have coded two different methods and
get same syntax missing operator error. Have searched postings for clues but
need a little guidance:

stLinkCriteria = "IncidentDate = Between #" & Me.startdate & "# and #" &
Me.enddate & "#"

or

stLinkCriteria = "IncidentDate " & ">=#" & Me.startdate & "# and <=#" &
Me.enddate & "#"
Click to expand...


The first one looks fine as long as both start and end dates
are specified as dates and not null.

The second one should get that kind of error because it is
missing second operand:
stLinkCriteria = "IncidentDate " & ">=#" & Me.startdate & _
"# and IncidentDate <=#" & Me.enddate & "#"

Neither will produce the correct filter if your machine is
set for a non-USA date format.
 
R

RoadKyng

Interresting as both give me a syntax error. I'll test your solution and see
what happens.

thanks Marshall
 
J

John Spencer

Pardon me, the first one is also incorrect. You can't use " = Between ".
You should drop the equals sign.

stLinkCriteria = "IncidentDate Between #" & Me.startdate & "# and #" &
Me.enddate & "#"

Other problems can arise if Me.StartDate and Me.Enddate are null (blank).
 
R

RoadKyng

Ah yes,
the dreaded extra = sign.

Thanks John. And I have added code requireing both date fields to be
non-null prior to running.
 
M

Marshall Barton

John said:
Pardon me, the first one is also incorrect. You can't use " = Between ".
You should drop the equals sign.

stLinkCriteria = "IncidentDate Between #" & Me.startdate & "# and #" &
Me.enddate & "#"

Other problems can arise if Me.StartDate and Me.Enddate are null (blank).
Click to expand...


Good catch John. It's always nice to have someone
straighten out my mistakes.
 
Top