Code to open report from Combo box

[nuttychick]

Hope someone can help

I have a database that records different types of Hardware Calls made.
There are reports based on crosstab queries.

I have a form that the user can specify the dates to run the report from, before or between. The code I have used to do this is below.
This works fine.
However I would now like to add a combo box with a list of the reports available, so the user can specify the dates and also the report they want to see.

I have changed the stdocName to read from the combo list. However if I select a report and run the cmd I get this error.

Run-time error '3070'
The Microsoft Jet database engine does not recognise '[forms]![DateSelection]![Date1]' as a valid field name or operator.

The quoted part differs depending what dates you enter.

How do I tell the cmd button to open the report specified in the combo and use the date criteria set.

If I use the dbug and step into then I can see that the code is picking up the correct report.

Any help would be much appreciated.


Private Sub Command16_Click()
'On Error GoTo Err_Command16_Click

Dim stDocName As String

stDocName = [Forms]![DateSelection]![Report List]

If IsNull([Forms]![DateSelection]![Date1]) And IsNull([Forms]![DateSelection]![Date2]) Then
MsgBox "There are no dates entered", vbOKOnly

End If

If Not IsNull([Forms]![DateSelection]![Date1]) And IsNull([Forms]![DateSelection]![Date2]) Then
DoCmd.OpenReport stDocName, acPreview, , "[HWCallDate] >= [Forms]![DateSelection]![Date1]"

End If

If IsNull([Forms]![DateSelection]![Date1]) And Not IsNull([Forms]![DateSelection]![Date2]) Then
DoCmd.OpenReport stDocName, acPreview, , "[HWCallDate] <= [Forms]![DateSelection]![Date2]"

End If

If Not IsNull([Forms]![DateSelection]![Date1]) And Not IsNull([Forms]![DateSelection]![Date2]) Then
DoCmd.OpenReport stDocName, acPreview, , "[HWCallDate] >= [Forms]![DateSelection]![Date1] and [HWCallDate] <= [Forms]![DateSelection]![Date2]"

End If


Exit_Command16_Click:
Exit Sub

'Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub

 

[nuttychick]

Tried it....

Thank you Pat

I have changed the code to include yours and when selecting different reports it does now work. However it is not providing the results depending on the dates provided.

All the records are shown in the reports.....

Any ideas?

Can you confirm I have changed the code correctly

This.....
If Not IsNull([Forms]![DateSelection]![Date1]) And IsNull([Forms]![DateSelection]![Date2]) Then
DoCmd.OpenReport stDocName, acPreview, , "[HWCallDate] >= [Forms]![DateSelection]![Date1]"I have changed to this....
If Not IsNull([Forms]![DateSelection]![Date1]) And IsNull([Forms]![DateSelection]![Date2]) Then
DoCmd.OpenReport stDocName, acPreview, , "[HWCallDate] >= #" & [Forms]![DateSelection]![Date1]"#"

Thanks

 

[cable]

Your missing an & before the last # but also:

You don't use Date2 at all? and you might need to reformat the date to be US (mm/dd/yyyy) format.

 

[nuttychick]

Apologies I have used the & before the final "#"

I have amended each section of the code (shown in pink on original post) to reflect the information given by Pat...

Or do I need to put this at the end of each section of code?
"[HWCallDate] >= #" & [Forms]![DateSelection]![Date1] & "# and [HWCallDate] <= #" & [Forms]![DateSelection]![Date2] &"#"

I have checked that all my date formats are the same, short date, they are british dates.
Would that mean I would have to change all the date formats in the database?
The date is an important part of the information held, I don't think the customer would like to see american dates on the reports.

Thanks

 

[cable]

No you don't need to change the format of the dates in general, just when you use them in WHERE clauses. ie #DateA# BETWEEN #DateS# AND #DateM#, those three date variables should be reformated as mm/dd/yyyy.

 

[Rich]

Your statement should look something like this
"[DatePurch]=" & "#" & Format(MatSub!DatePurch, "mm/dd/yyyy") & "#"

you do not need to reformat your original date field

 

[nuttychick]

More help....

Rich,

No quite sure how to use this statement, I'm very new at writing code, where should this go and what do I need to change it to?

Thanks

 

[cable]

Rich,

No quite sure how to use this statement, I'm very new at writing code, where should this go and what do I need to change it to?

Thanks

the change is on this line of your code:
DoCmd.OpenReport stDocName, acPreview, , "[HWCallDate] >= #" & [Forms]![DateSelection]![Date1]"#"

The last part of that line (ie after the last ,) is the WHERE clause of the openreport command. Rich gave you what your WHERE clause should look like, you'll need to change the field names (hwcalldate), and the control names (the forms!blah!blah) to suit your app.

 

[nuttychick]

Thanks Cable

OK,

Now I have the following code as my open report bit.

DoCmd.OpenReport stDocName, acPreview, , "[HWCallDate]=" & "#" & Format(Date, "Short Date")(Forms![DateSelection]![Date1]) & "#"

I also now get Run-Time error '13': Type mismatch.
I've looked at the help for this error, and I'm sure its trying to tell me something important, but I just don't understand a lot of it.

I've probably written it wrong....being completely inexperienced!

Help!

 

[nuttychick]

Thanks Pat,

This is the code you gave me first off, however trying it again now is giving different results.

If the report is NOT based on a cross tab then I am getting all the records.
If the report IS based on a cross tab then I am getting no records.

I'm soooo confused...why doesn't this work!?

When I step into the code it knows all the dates it need to, but it appears that when opening the reports this criteria is lost along the way....

Thats the last time I say "Yes, I think you can do that, it shouldn't be too difficult" !!!

Please can anyone try to turn this into this ?

 

[cable]

Thanks Pat,

This is the code you gave me first off, however trying it again now is giving different results.

If the report is NOT based on a cross tab then I am getting all the records.
If the report IS based on a cross tab then I am getting no records.

I'm soooo confused...why doesn't this work!?

When I step into the code it knows all the dates it need to, but it appears that when opening the reports this criteria is lost along the way....

Thats the last time I say "Yes, I think you can do that, it shouldn't be too difficult" !!!

Please can anyone try to turn this into this ?

Ok it sounds like you need to check that the query behind the report works the way your expecting. Open the report in design view, and then open the query behind it. Run the query, make sure it returns what you expect. Then manually add the date restricition (ie in the criteria row under the date field column put #18/05/2004#) and rerun the query. If the data looks ok then we can assume that the query and report are not at fault. Do the same for any other reports that you don't think are working correctly.

 

[nuttychick]

Queries and reports ok

Thanks Cable,

I can confirm that all the queries and the reports work fine if I specify the date criteria. I used >= 01/04/04 which returned 17 records (good stuff)

Not sure if this makes a difference, but because there were 17 records found it brings up 17 pages of report (all the same) a bit annoying...but not so worried about that.

So its definatley the code on the form.....any ideas?

 

[Rich]

You either have to use the Format function as I showed you, or change your queries to Parameter Queries and just reference the Form and control, if you use the second method, use the code builder for it will set the correct references for you