Prev: How do I determine if the FormHeader has focus in a continuousform ?
Next: crosstab query problem
From: Marshall Barton on 28 Jul 2010 16:29
divventknaa wrote:

>Has anyone got some examples of how to use stlink criteria when using it with DoCmd.OpenReport
>
>I used to have a good cribsheet of the syntax for numeric, string, date data etc. but it's vanished
>I have found some examples when you are calling the report using the contents of a text box on a form
>but this is straightforward using the underlying query to filter, primarily in this case [Field1] = 2
>AND [Field2] = 1


The general rule is for **values**, either typed directly or
concatenated from a variable/control need to be enclosed in:
number nothing
text either single or double quoted (' or ")
date/time # signs (and be either USA style (m/d/yyyy)
or ISO style (yyyy-m-d)

E.g.
.... = "[number field]=" & numbervariable

.... = "[text field]=""" & stringvariable & """ "
or
.... = "[text field]='" & stringvariable & "' "
or
.... = "[text field]=""" & Replace(stringvariable, """""",
"""") & """ "
or
.... = "[text field]='" & Replace(stringvariable, "'", "''")
& "' "

.... = "[date field]=" & Format(datevariable,"\#yyyy-m-d\#")

the reason dates are so complicated is because Access uses
your user's Windows regional settings to convert a date
value to a string and the result of that conversion may not
be the date that was intended.

--
Marsh
From: divventknaa on 29 Jul 2010 16:22
Marshall, thsnks for info.

On the dates thing I saw one solution by putting a CHR$ statement either side which stops the date
translation anomaly.. Not had cause to use it though.

I will save your dit as a Word file and make several copies this time. As I only dip into access now
and again, if I haven't got a handy routine saved the grey hair and age takes over and I forget
things !

J


--
--------------------------------- --- -- -
Posted with NewsLeecher v3.9 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

From: Marshall Barton on 30 Jul 2010 12:16
divventknaa wrote:
>On the dates thing I saw one solution by putting a CHR$ statement either side which stops the date
>translation anomaly.. Not had cause to use it though.
>

If it really was a date and not a string, the Chr$ was the
same as using "#" so it would suffer the same Windows
settings conversion issues. Either will work dine on a
system set to use USA or ISO style dates, but, since you can
not control what users might do on their systems, you should
use the Format function to guarantee it will work on all
systems regardless of the Windows settings.

--
Marsh
 | 
Pages: 1
Prev: How do I determine if the FormHeader has focus in a continuousform ?
Next: crosstab query problem