To Syntax Guru (between... and...)

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi anyone,
Here what this is about:

stLinkCriteria = "[MyDate]=" & Between & "Date()" And "Date
() -7"

It says, "Type Mismatch"..
stLinkCriteria as String.

What is the right systax for that expression? Thank you in
advance for your help.
By the way, can I use the same systax in Query builder for
criteria? Thank you.
 
Looks like you are trying to do this in code.
stLinkCriteria = "[MyDate] Between Date() And Date() -7"
 
John,

stLinkCriteria = "[MyDate] BETWEEN #" & Date() & "# AND #" & Date() -7 & "#"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
stLinkCriteria = "[MyDate] BETWEEN #" & Date() & "# AND #" & Date() -7
& "#"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...

Just as a matter of interest, what is the normal date format in Oz? I was
under the impression that it was D-M-Y, in which case this code will fail
because VBA obeys regional settings when coverting Date() to text. Next
week, for example, this will do

? stLinkCriterion

[MyDate] BETWEEN #01/04/2004# AND #25/03/2004#

which will produce some interesting, if not expected, results once Jet gets
hold of it.

Just a thought


Tim F
 
Hi Tim,

Yes, it is DMY in Oz, but predominately the people who use the newsgroups
use US format, so unless they specifically state otherwise, I try to avoid
confusing them.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Tim Ferguson said:
stLinkCriteria = "[MyDate] BETWEEN #" & Date() & "# AND #" & Date() -7
& "#"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...

Just as a matter of interest, what is the normal date format in Oz? I was
under the impression that it was D-M-Y, in which case this code will fail
because VBA obeys regional settings when coverting Date() to text. Next
week, for example, this will do

? stLinkCriterion

[MyDate] BETWEEN #01/04/2004# AND #25/03/2004#

which will produce some interesting, if not expected, results once Jet gets
hold of it.

Just a thought


Tim F
Click to expand...
 
Yes, it is DMY in Oz, but predominately the people who use the
newsgroups use US format, so unless they specifically state otherwise,
I try to avoid confusing them.
Click to expand...

<gulp />I hesitate to nit-pick with a MVP, but it is less confusing to
provide a properly-working code snippet, than to have a naive user trying
to track down a notoriously hard-to-find bug?

The correct answer is not so confusing:

stLinkCriterion = "[MyDate] BETWEEN " & _
Format(Date(), "\#yyyy\-mm\-dd\#") & " AND " & _
Format(Date()-7, "\#yyyy\-mm\-dd\#")

which can be copied as boilerplate by someone who does not understand it,
and can be learned from by those who do.

Just a thought :-)
B Wishes



Tim F
 
Tim,

You're probably right. Perhaps it was a bad judgement call.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Tim Ferguson said:
Yes, it is DMY in Oz, but predominately the people who use the
newsgroups use US format, so unless they specifically state otherwise,
I try to avoid confusing them.
Click to expand...

<gulp />I hesitate to nit-pick with a MVP, but it is less confusing to
provide a properly-working code snippet, than to have a naive user trying
to track down a notoriously hard-to-find bug?

The correct answer is not so confusing:

stLinkCriterion = "[MyDate] BETWEEN " & _
Format(Date(), "\#yyyy\-mm\-dd\#") & " AND " & _
Format(Date()-7, "\#yyyy\-mm\-dd\#")

which can be copied as boilerplate by someone who does not understand it,
and can be learned from by those who do.

Just a thought :-)
B Wishes



Tim F
Click to expand...
 
Tim,

You're probably right.
Click to expand...

Hey, there's enough USian hegemony on these boards as it is. We English-
speakers need to stick together..!

All the best


Tim F
 
Back
Top