Query export problems

[Jedat]

Hi all,

I've got a db that has been given me to develop queries for to fulfill requests for Management Information.



I have set some queries up and have them set to export into an excel sheet, so far so good. However 10 of these queries need to be run daily and then exported. If i go into them individually and change the date criteria, the correct data is then exported into excel.

To make this process easier for the end user i have amended the date criteria field in the query to read [forms]![formname]![startdate] this takes the date from an unbound text box on the form.

I have added a command button to the form with the following code behind to run the queries

Start Code

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryFirstQuery"
DoCmd.OpenQuery "qrySecondQuery"
DoCmd.OpenQuery "qryThirdQuery"
DoCmd.OpenQuery "qryFourthQuery"
DoCmd.OpenQuery "qryFifthQuery"
DoCmd.OpenQuery "qrySixthQuery"
DoCmd.OpenQuery "qrySeventhQuery"
DoCmd.OpenQuery "qryEighthQuery"
DoCmd.OpenQuery "qryNinthQuery"
DoCmd.SetWarnings True

''End Code

This works and everything is fine, however if i then open the excel spreadsheet the exported queries all report a nil total as if they are not taking the date through.

Has anyone any ideas how i can do this?

[KathyL]

Hi all,

I've got a db that has been given me to develop queries for to fulfill requests for Management Information.

I have set some queries up and have them set to export into an excel sheet, so far so good. However 10 of these queries need to be run daily and then exported. If i go into them individually and change the date criteria, the correct data is then exported into excel.

To make this process easier for the end user i have amended the date criteria field in the query to read [forms]![formname]![startdate] this takes the date from an unbound text box on the form.

I have added a command button to the form with the following code behind to run the queries

Start Code

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryFirstQuery"
DoCmd.OpenQuery "qrySecondQuery"
DoCmd.OpenQuery "qryThirdQuery"
DoCmd.OpenQuery "qryFourthQuery"
DoCmd.OpenQuery "qryFifthQuery"
DoCmd.OpenQuery "qrySixthQuery"
DoCmd.OpenQuery "qrySeventhQuery"
DoCmd.OpenQuery "qryEighthQuery"
DoCmd.OpenQuery "qryNinthQuery"
DoCmd.SetWarnings True

''End Code

This works and everything is fine, however if i then open the excel spreadsheet the exported queries all report a nil total as if they are not taking the date through.

Has anyone any ideas how i can do this?

If you have the form loaded, and the date on it.... and then you just run one of the queries from the object list... does it give you results for the one query? It should. If not, perhaps an error in the query.

[pkstormy]

Sometimes it has to do with the criteria and the wrong form/field name. I'd run each one manually as KathyL suggested.

You can also check that startdate is formatted as a date field on the form and make sure your criteria is under a date field type column in the query (if it is actually based on a date field).

I'd also test to see if startdate is null before executing the queries since date type fields in queries sometimes don't work well with null values.

[Jedat]

Thanks for the replies, the query does return results when run individually with the form open or when run through the form itself. The problem occurs when i try to export the query into excel.

As soon as i export the queries the figures revert to 0, i think it is more to do with my excel than access to be honest, i will be investigating that route as well with my IT bods, but any other suggestions gladly welcomed.

[pkstormy]

Are you sure it's not a Yes/No field? These would export as 0 or -1. Otherwise I'd try exporting as a csv file to see what the value is.