Criteria question

  • Thread starter Thread starter Gavin
  • Start date Start date
G

Gavin

Hi

Should the following criteria work in a query?

Between [Forms]![Myform]![Myfield] And [Forms]![Myform]!
[Myfield]

I am trying to set the criteria without using a pop-up
parameter box, i have the date values i want to search for
in two text boxes on a form.

Is there any other way to set a queries criteria, i have
several options for my users to select ie. last 7 days,
last month, last 6 months, and i want change the criteria
depending on what they select.

Please excuse my stupidity if there is a simple way around
this, i thought the methods i originally used would be ok.

Thanks for your help

Gavin
 
Hi,


That would work if you can use DoCmd, or a DXXX function (like DLookup,
DSum, etc.). If you use CurrentDb or a database object,
Forms!FormName!ControlName will be consider as parameter that you would
have to supply by another mean, or by embedding the value rather than the
reference, in the string:

strSQL=".... WHERE ... fieldname BETWEEN " & Forms!FormName!ControlName
& " AND " & Forms!FormName!ControlName2 & " .... "

but doing so, you have also to supply the right delimiter (since the
"values" are now appearing as constant, not as referred value) in the US
format ( mm-dd-yyyy for date, a decimal dot rather than a coma, quote or
double quote for string, with proper handling if that delimiter already
appear in the initial string, etc ). Be conscious that the default format is
accordingly to the actual Regional Setting on the PC on which run your code,
something you do not control... something working in US may stop working in
UK. All those cares are not necessary if you use DoCmd or Dxxx, and keep
Forms!FormName!ControlName as "reference", inside the SQL statement.


Hoping it may help,
Vanderghast, Access MVP
 
Gavin

Michel has already provided larger detail, so I'll only point out a
nit-picking tiny matter --

You used the same fieldname ([Myfield]) twice, in both references. If you
want to use Between...And..., you'll need two separate fields.
 
Back
Top