VBA code for Where Condition

[surfside1]

I have a form that has a combo box that allows the user to select which report and a date range that they want for the report. The report was built using a table. (PHV I moved this out of the other thread because it needed a different subject title).

I'm having problems with the where condition on the DoCmd.OpenReport. I capture the combo values of both the report they want and the dates. I'm not sure how to place all values in the where condition. I've tried it different ways - 1 to create a str_Where variable that will capture all values, then use that variable in the DoCmd statement and another to break down all values into separate str_Wheres and bring them all together in the Where condition. Below is my latest attempt to bring the separate values to create one where condition, a date range, and a status. I just don't know how to code it correctly either way. I saw that the values are good, then tried to bring them all into one statement:
If str_Report_Selected = "Listings Sold" Then
str_Report_Title = "Listings Sold"
str_Where1 = "tbl_Listings.Status = '2 - Sold'"
str_Where2 = "tbl_Listings.Start_Date >= Me.combo_Start_Date"
str_Where3 = "tbl_Listings.End_Date <= Me.combo_Start_Date"
End If

When I try to bring the 3 different "Wheres" together the value created looks like this:
'(tbl_Listings.Status = '2 - Sold'tbl_Listings.Start_Date >= Me.combo_Start_Datetbl_Listings.End_Date <= Me.combo_Start_Date)'
All running together. I tried various things like " ", AND, & - But I'm not sure what to do. The good part is it is finding the table fields, I just need to know how the format the Where statement.

str_Where = str_Where1 + ";" str_Where2 + ";" str_Where3
DoCmd.OpenReport str_Report_Name, acViewPreview, , str_Where

Just not sure of the syntax. Thanks!

 

[Zion7]

You couldn't find this basic expression anywhere?
Help/On-Line/other forums?


str_Where1 = "Status = '2 - Sold'"
str_Where2 = " AND Start_Date >= #" & combo_Start_Date & "#"
str_Where3 = " AND End_Date <=#" & combo_Start_Date & "#"

 

[TheAceMan1]

How are ya surfside1 . . .

If [blue]Start_Date[/blue] and [blue]End_Date[/blue] represent a span of time, then the logic of . . .

[blue]Start_Date >= combo_Start_Date AND End_Date <= combo_Start_Date[/blue]

. . . [purple]can never be true![/purple]

[blue]Am I missing something here?[/blue]

See Ya! . . . . . .

 

[surfside1]

Hey AceMan or Anyone,
Sorry about that AceMan, I did catch that, just not until after the posting. Maybe you or someone else could help me with something else. I'm thinking that I don't understand how a table's data can be captured in code based on a selection made on a form.

What I mean - for example:
My form was not created with a table, but since it didn't work I have the record source set to the table that contains the data I need. What I want to do is have someone select a date range, then select which report they want. When they select a report, such as "Sold" or "Not Sold", then it will set a variable for the appropriate title for the report (same report is used for all). And the date they enter (or it defaults to Date) are used to allow the user to look at all listings that “Sold" or any other status during that time period. That date is stored in the table as End_Date. I am thinking that I can not actually see the table values there. What I actually see in the Where when debugging is Where = “((tbl_Listings.Status = ‘2 – Sold)(tbl_Listing.End_Date)”
No AND.
Maybe someone could set me straight as far as “when does Access recognize that you want to see the table values in VBA. Do I need to create the form with the table and then put the key on the form (hidden so they don’t see?) I’m lost, and if anyone can point me to the info that explains this, that would be great. I have the Access 2003 Bible – I don’t find Access’s help file much help.)

My code is as follows: ( I wanted the code to be shorter):

If str_Where = "" Then (this means that they want "All Listings)
DoCmd.OpenReport str_Report_Name, acViewPreview, , str_Where
ElseIf str_Where = str_Where & " AND ((tbl_Listings.End_Date)>=# " _
& Me.combo_Report_Start_Date & "# AND (tbl_Listings.End_Date)<=#))" _
& Me.combo_Report_End_Date & "#))" Then
DoCmd.OpenReport str_Report_Name, acViewPreview, , str_Where
Else
MsgBox "There are no Listings matching the report dates provided. " & _
"Please select the date range that you want to view the listings " & _
that were active during those dates. " & _
"These reports could be helpful when filing the sales tax.", vbCritical
Me.combo_Report_Start_Date.SetFocus

End If

 

[snayjay]

Not sure if this would fix anything, don't have time to really check this code... but I did notice something wrong with your parenthesis.

Copied from you post
Follow the colors

str_Where = str_Where & " AND [b][COLOR=red]([/color][COLOR=green]([/color][/b]tbl_Listings.End_Date[b][COLOR=green])[/color][/b]>=# " _
& Me.combo_Report_Start_Date & "# AND [b][COLOR=orange]([/color][/b]tbl_Listings.End_Date[b][COLOR=orange])[/color][/b]<=#[b][COLOR=red])[/color][/b])" _
& Me.combo_Report_End_Date & "#))"

You'll notice 3 parenthesis at the end that I didn't color... they should have corresponding open parenthesis "(" somewhere in your code, but I didn't see them.

 

[TheAceMan1]

[blue] . . . I'm thinking that I don't understand how a table's data can be captured in code based on a selection made on a form . . . Maybe someone could set me straight as far as “when does Access recognize that you want to see the table values in VBA.[/blue]

Well . . . [green]for a learned access programmer[/green] no problemo (we know when & how to tell access). For you (particularly as it relates to the secnario you've presented), I'll do the best I can to explain without getting too lengthy.
[ol][li]First . . . there's the concept of [blue]Bound[/blue] & [blue]Unbound[/blue] forms (I could say reports as well but 99.999% of the time, they are bound). So I'll explain the bound difference using forms as the example. the same will hold for reports.
[ol a][li][blue]Bound Form[/blue] - A bound form has its [blue]Record Source[/blue] set to a table, or set to a query based on a table. Textboxes on the form have their [blue]Control Source[/blue] set to a name within the table or query. It is the [blue]Control Source[/blue] here that binds the form to the table. All in all with this secnario if you change data in a record or add a new record its directly changed or added by access to the table whenever you set the focus to a different record or the [blue]add new[/blue] record line.[/li]
[li][blue]UnBound Form[/blue] - Here the forms [blue]Record Source[/blue] is empty, as well as the [blue]Control Source[/blue] of the textboxes. Again . . . it is the [blue]Control Source[/blue] of the textboxes having no value that makes the form [blue]unbound[/blue]! The difference here is that (thru code) its up to the programmer to do everything access does for bound. That is . . . Post data to the controls and save/update accordingly.[/li][/ol][/li]
[li]Now . . . if the above makes sense then you should see that what you need for opening your reports is an [blue]UnBound[/blue] Form with [blue]two unbound textboxes[/blue] (StartDate & Enddate). A [blue]ListBox[/blue] with the names of the reports and a [blue]command button[/blue] for execution! . . . I hope you can see this! [/li]
[li]Now . . . for your [blue]DoCmd.OpenReport[/blue] gross error! . . .
Your using the 4th arguement wich comprises a [blue]wherecondition[/blue] against the [blue]Record Source[/blue] of the report ([green]thats how you know the table here![/green])!

Here's what microsoft help saids about this arguement:

[blue][purple]wherecondition[/purple] - A string expression that's a valid SQL WHERE clause without the word WHERE.[/blue]

What your attempting to do in code remains to be seen. Perhaps if you just explain this a little more throughly.[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[surfside1]

AceMan thanks for taking the time for such a good explanation! It was just my syntax that snayjay pointed out.

I have a bound report, even though it sounds like I didn't need to based on what you said. I did everything in code. The Access error that I got when it couldn't find my table value during debugged, made me question if the approach was the way I should take.

But your writeup really helped to understand it even more. Thanks!