Constructing criteria strings with a form 1

[meierswa]

I am trying to get a form to control the criteria in a query and then view the corresponding report. Specifically, I would like to enter the dates, choose the processors, and choose the documents and then view the report.

Visit the link the see a screen shot of my query and form.

http://plaza.ufl.edu/meierswa/help.htm

I was told the best way to accomplish this would be to have the form construct a criteria string itself (in code) from its own control values and not use a query at all. Unfortunately, I am code illiterate.

Can anyone help? Any assistance would be appreciated.

 

[randysmid]

Hi,
I wrote an FAQ on this exact topic in the Reports section. Here is the direct link to it: faq703-2696
HTH,
Randy Smith
California Teachers Association

 

[meierswa]

Randy,

Thanks for the suggestion. Unfortunately, my report is coming up blank. I updadted the screen shots on the link in the first posting with my button code, qry, and frm. Any insight as to why my report is blank?

Thanks,
Andy

 

[randysmid]

Hi,
Yes, I think I know the problem. I went to the link you had in the upper part of this posting, and discovered that you are sending dates as a text box. You will need to encase the dates inside of pound signs (#). So, your criteria will look like this:
Between "#" & [Forms]![frmActivity]![txtStartDate] & "#" & ......

As an experiment, hardcode the date range. For instance, set the criteria to be: Between #01/01/2002# and "#12/31/2002# (or some other range that you know has records) HTH,
Randy Smith
California Teachers Association

 

[meierswa]

Randy,

The form and report work if the data range is hardcoded.

I tried changing my criteria to the following:

Between "#" & [Forms]![frmActivity]![txtStartdate] & "#" And "#" & [Forms]![frmActivity]![txtEnddate] & "#"

However, it is still coming up blank.

Any suggestions?

 

[randysmid]

Hi,
I would next "debug" the query itself. I would do this by going into the query tool, and opening the query that is not working correctly. Next, change all the criteria to actual values instead of "Forms![fldActivity]" etc. You can then run the query by clicking on the exclamation point (!). If data is returned, and it looks correct, then gradually add one field at a time (and test with the ! button) until it no longer works. Obviously, whatever field you added most recently is the culprit.

HTH,
Randy Smith
California Teachers Association

 

[randysmid]

Hi,
Another point occurs to me. It could be that your datatypes are not correct. For instance, you may have a criteria field that is "integer" datatype, but the code from your form is sending "string" datatype info. If this is the case, there are a number of conversion functions available, such as "CInt" which converts string to integers.

HTH,
Randy Smith
California Teachers Association

 

[randysmid]

Hi,
I just reread your code very carefully, and found an error in it. Here is what you have:
....![txtStartdate] & "#" And "#" & ....

It should be:
.....![txtStartdate] & "# And #" &

In other words, the pound signs need to wrap around the text box as it is being received from the form. The "And" is part of the "between' statement, and should therefore be also enclosed.
HTH,
Randy Smith
California Teachers Association

 

[meierswa]

I think it has to do with my form. Both my list boxes generate their values from queries. I have the row source type set to table/query and the row source set to the respective query.

Do I need to put a command before my criteria expression (i.e. Somecommand [Forms]![frmActivity]![lstProcessor])?

 

[meierswa]

The following expression has an invalid date value.

Between #" & [Forms]![frmActivity]![txtStartdate] & "# And #" & [Forms]![frmActivity]![txtEnddate] & "#

 

[randysmid]

Hi,
This is what you just sent to me:
Between #" & [Forms]![frmActivity]![txtStartdate] & "# And #" & [Forms]![frmActivity]![txtEnddate] & "#

It should be:
Between "#" & [Forms]![frmActivity]![txtStartdate] & "# And #" & [Forms]![frmActivity]![txtEnddate] & "#"

In other words, the first and last pound signs need to have double quote marks surrounding them.

RE: Do I need to put a command before my criteria expression (i.e. Somecommand [Forms]![frmActivity]![lstProcessor])?
The answer is No, unless the field is something other than string. The rule is that whenever you are getting some value from a form, it will always be a string datatype. If you are trying to use it as a criteria for something other than a string, then it will not work correctly. For instance, here are some criteria examples:
Long type: CLng([Forms]![frmActivity]![txtActivity])
Integer type: CInt([Forms]![frmActivity]![txtActivity])
Currency type: CCur([Forms]![frmActivity]![txtActivity])
Boolean type: CBool([Forms]![frmActivity]![txtActivity])
Date type: CDate([Forms]![frmActivity]![txtActivity]

You can find more information in VBA help (enter "conversion functions&quot.



HTH,
Randy Smith
California Teachers Association

 

[meierswa]

Between "#" & [Forms]![frmActivity]![txtStartdate] & "# And #" & [Forms]![frmActivity]![txtEnddate] & "#"

generates the following error:


<b>You did not enter the keyword And in the Between...And operator. </b>
The correct syntax is as follows:
expression [Not] Between value1 And value2

 

[randysmid]

Hi,
I wonder if you can do a simple experiment.

Here is what we have right now:
Between &quot;#&quot; & [Forms]![frmActivity]![txtStartdate] & &quot;# And #&quot; & [Forms]![frmActivity]![txtEnddate] & &quot;#&quot;

Can you please change it to this, and see what happens:
Between CDate([Forms]![frmActivity]![txtStartdate]) And CDate([Forms]![frmActivity]![txtEnddate])

Essentially, we are using the CDate function to convert the two text strings to a date.

HTH,
Randy Smith
California Teachers Association

 

[meierswa]

The query works with the new date criteria you recommended and the processor criteria (i.e. Forms![frmActivity]![lstProcessor]).
However, everytime I try to specify criteria for the third field (i.e. Forms![frmActivity]![lstDocument]) I get the following error when I try and run the query:

The expression is typed incorrectly, or is to complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

 

[randysmid]

Hi,
Is it possible that your list box is set to &quot;Simple&quot; under Multi-Select (found under Other in Properties)? To insure that the user only selects one item, you will need to change this property to &quot;None&quot;.

Please reply to this post if the user is allowed to select multiple items to be sent to the query. That coding will be a bit more difficult, but not impossible.

HTH,
Randy Smith
California Teachers Association

 

[meierswa]

I have the Multi Select set to extended for both list boxes. The user needs to be able to select multiple items.

 

[randysmid]

Hi,
Here is the code that will put all the selected items into a string (strProcessors), which then becomes the criteria source in the query:


Dim vItemsSelected As Variant, strProcessors As String

For Each vItemsSelected In Me!lstProcessor.ItemsSelected
' Place single quotes around the processor id
' because the ProcessorID field is string datatype.
strProcessors = “’” & strProcessors & “’ or ‘“ & _
Me![lstProcessor].ItemData(vItemsSelected) & &quot;' or &quot;
Next

‘ at this point in the code, all selected items have been identified

' Remove the OR string from the end of the filter
' condition if a filter exists.
If strProcessors <> &quot;&quot; Then
strProcessors = Left(strProcessors, Len(strProcessors) - 4)
End If

HTH,
Randy Smith
California Teachers Association

 

[meierswa]

I am code illiterate. Where do I put this?

 

[randysmid]

Hi,
You need to put this in the same place you have added your other code to send the criteria to the query. Generally, most people have a command button to run the report (e.g., &quot;RUN REPORT&quot. I use the clicked event for this button to put my code. That way, the user can go back and forth all they want to modify the criteria until they get it just the way they want it. Then they click on the &quot;RUN REPORT&quot; button, and off it goes.
HTH,
Randy Smith
California Teachers Association