General query filter question!

[Khao]

Hey hey,

I've been asked to create my company's database and I'm a little out of my depth when it comes to queries & reporting.

My query (well... table) has the following fields: Date Invoiced, Product, Quantity, Subtotal Price, JobCode, State

I've been able to filter the report with dates by simply using the date criteria " Between [Date from] And [Date to] "... essentially two boxes pop up asking me which dates to choose between.

However, I would also like to be able to filter by state and job code. The job code contains two entries (DM / SW) and I'd like to have the option to choose either one or both.

Furthermore, I need to be able to filter depending on which state the job was in (QLD, NSW, VIC, SA, WA, NT, ACT, TAS) or all of them. It appears that all basic query criteria is restricted to finding specific values.

The only option I know of is to create dozens of queries depending on which specific filter I would like... But that seems highly impractical and I'm sure that someone that is far more knowledgable than I could be of assistance. Ideally, a form with drop down boxes would be preferred but if I'm limited to boxes popping up asking for parameters then that is fine



My skills in access are rather rudimentary so if you could break it down that would be amazing!

Current SQL:

SELECT [Mock Jobs].[Date Invoiced], [Mock Jobs].Product, [Mock Jobs].State, [Mock Jobs].Quantity, [Mock Jobs].[Subtotal Price], [Mock Jobs].[Job Code]
FROM [Mock Jobs]
WHERE ((([Mock Jobs].[Date Invoiced]) Between [Date from] And [Date to]) AND (([Mock Jobs].[Job Code])="deck-max"));

Regards,

Harry.

[June7]

Review
http://datapigtechnologies.com/flash...mtoreport.html
http://datapigtechnologies.com/flash...tomfilter.html

The examples don't include date criteria. Try this for your date range:

Between Nz([Date from],#1/1/1900#) And Nz([Date to],#12/31/2900#)

[Khao]

Review
http://datapigtechnologies.com/flash...mtoreport.html
http://datapigtechnologies.com/flash...tomfilter.html

The examples don't include date criteria. Try this for your date range:

Between Nz([Date from],#1/1/1900#) And Nz([Date to],#12/31/2900#)

I am tearing my hear out with this simple task and I am at loss with words as to why it won't work.

I create the button on a form. Exit out of the wizard and go to event properties. Click the 3 dots and go to code builder. Enter "DoCmd.OpenReport "MainReport", acViewPreview" in between the code. Close it and return to the form. BUT THE REPORT WON'T OPEN! The button does absolutely nothing. Can anyone explain why?

I've done everything to the tee that the first flash video explains and I have no idea why this damn report won't open... doesn't make sense.

=[

[Khao]

Might I also add that I can get the report to open if I use the wizard but not if I enter the code manually.

[June7]

Do you want to provide db for analysis? Follow instructions at bottom of my post.

[Khao]

Do you want to provide db for analysis? Follow instructions at bottom of my post.

I'll do it first thing Monday morning, its on the work server. Thanks a lot mate

[Khao]

Should be attached.

[June7]

I have your db. Now tell me which report is the issue. What button opens the report?

[Khao]

Ah I deleted the button and report thinking it would have been easier to start from scratch. The button was located on the form 'Main' and the report pulled information from 'Mock Jobs' - information being Date Invoiced, State, Product, Job Code. The filter needed is in the original post.

[June7]

Can't analyze your issue if there is nothing to look at.

Me creating a working button and report does nothing to help you.

[Khao]

Can't analyze your issue if there is nothing to look at.

Me creating a working button and report does nothing to help you.

I've reuploaded it with the issue I originally had. Button just won't work and I haven't been able to see if the report works because the button doesn't.

[June7]

The code is associated with the combobox Click event. There is no code associated with the button.

[Khao]

The code is associated with the combobox Click event. There is no code associated with the button.

Excuse my lack of knowledge, but what's the difference? I'm pretty sure I followed exactly what the video did and he does "event -> on click" and his works and mine doesn't.

[June7]

Every control has its own events. You click the combobox and its Click event runs. You click the button and nothing happens because there is no code.

Beginning of the 'Filter Report by Form' tutorial shows setting the command button Click event, not combobox. The combobox isn't even created at that point.

[Khao]

Every control has its own events. You click the combobox and its Click event runs. You click the button and nothing happens because there is no code.

Beginning of the 'Filter Report by Form' tutorial shows setting the command button Click event, not combobox. The combobox isn't even created at that point.

Oh yeah my mistake - I just changed the buttons on click to the code below but i'm still getting nothing when I click it.

 
Private Sub Command67_Click()
DoCmd.OpenReport "MainReport", acViewReport
End Sub