Using VB variables in Query

  • Thread starter Thread starter Brian Hoops
  • Start date Start date
B

Brian Hoops

I have some variables calculated in my VB code that I need to incorporate
into the search criteria of a query. How exactly do I go about doing this?

I did find something about using a VB function in your criteria (ie.
blah() ), and that worked fine for one field (string->text), however I'm
stuck while trying to create
varDate = ">=#" & Format(rptBegDate, "Short Date") & "# and <=#" &
Format(rptEndDate, "Short Date") & "#"

The query says "Data type mismatch in criteria expression."

Any direction would be appreciated!

-Brian
 
Use two variables, one for each criterion. Remember that your query's
criterion expression must repeat the field name for each criterion.
 
I understand the first half, although it won't work in this case. Below is
the code in the function, so you can see what I'm attempting to do. I'm
thinking some kind of If...Then.
The second half just gave me a headache, can you clarify?

Thanks
-Brian

'If rptAllDate = True Then
' varDate = ???need something here to make sure all records show
'Else
' varDate = ">=#" & Format(rptBegDate, "Short Date") & "# and <=#" &
Format(rptEndDate, "Short Date") & "#"
'End If
 
How about this, if I'm understanding what you're doing:

varDate = "Between #" & Format(IIf(rptAllDate=True, _
"12/31/1899",rptBegDate), "Short Date") & _
"# And #" & Format(IIf(rptAllDate=True, _
"12/31/2999",rptEndDate), "Short Date") & "#"
 
Nope, still the data type mismatch, and then after this date field there are
still another 5 (yes/no and number) fields that are causing errors.

Perhaps I'm going about this all wrong. The user doesn't want to manually
enter search criteria to create a report, therefore I created a form with
option groups to basically do the same thing. For dates the options are
'All dates' or 'Between xxxx and xxxx'. The OK button analyzes the options
and set's the rptXXXX booleans to their values.

Is there a way to set the SQL search string for the query exclusively from
VB rather than trying to analyze and import the variables? This is my first
application in Access -- I usually keep the db in access and run the
application in VB.net, but that isn't an option here.

Thanks!

-Brian
 
Oops, posted too soon. I got the date fields working by breaking it up into
two functions per your original suggestion -- if they wanted all then I used
the 1899 and 2099 dates. All I could say was 'Duh' when that worked
(K.I.S.S.?).

Now, to the yes/no fields ... still working on them.

-Brian
 
I got all fields to work by breaking them each up into two separate
functions and referencing both in the criteria. Although this is a little
messier than I would have liked, it works and that's what matters to me.
For example...
Yes/No
Function1
if true =-1
else =0
Function2
if true=-1
else=-1
Criteria = function1 or function2

String and number
function
if true="foo"
else="*"
Criteria=Like function

Date
function1
if true=BegDate
else=1/1/1899
Function2
if true=EndDate
else=1/1/2099
Criteria=>=function1 and <=function2

I don't know if anyone will every run into this same issue, but if they do,
this is how I did it.
KEN - Thanks for the help in setting me on the right path!!

-Brian
 
Back
Top