Export to Excel 2000

[Richard_Marx]

Did you cut and paste, or did you type it in?

I saw this in the middle of that subquery:

FROM cobinedTable

I also note a missing space before AND in this part:

Between[forms]![form1]![start]AND [forms]![form1]![end]

I typed it in..those are just typo's on my part. Sorry guys.

[Dal Jeanis]

Good enough. Okay, I'd go back to simplifying the query to something I'm sure would work... loading temp variables for each of the parameters, making sure the ... hmmmm...

Hey, try this. Successive approximation.

1) Make a really simple query, against one table with hardcoded parameters or no parameters at all, returning two columns and about ten records.
2) Make a junk xls.
3) Run the code against that query and that xls.

If the above works, do it with a junk copy of the real xls. From this, you will prove whether the problem is the code, the query or the xls. Keep inching the code closer to what you need, one item at a time.

I expect the problem is in the query, but you'll have to find what it is.

[ssanfu]

Originally Posted by Dal Jeanis

I also note a missing space before AND in this part:
Code:

Between[forms]![form1]![start]AND [forms]![form1]![end]

Not to be a picker of nits, there is also a missing space after "between"

For example, I have a query that this is the SQL for:
Code:

SELECT COUNT(combinedTable.UserID) As CountOfUID FROM cobinedTable WHERE (((combinedTable.UserID)="RM1219") AND ((combinedTable.LogInDate) Between[forms]![form1]![start]AND [forms]![form1]![end]
If I input parameters on the user form and run my ShowData query all is well, but it seems that when I Select Dates, and attempt to export to Excel that is where the underlying issue ensues.

As Dal pointed out to me one time, you have to be specific. Is this query a saved query or a query in code? What is its name?

My ShowData query is running about 8 subquery's to show all of the data in ONE centralized location.

** Would you elaborate more on this? Is the "ShowData" query a union query? What does the SQL of the other 8 queries look like?
Why do you have 8 sub-queries? (not questioning, just curious - trying to understand)

When I attempt to run the code, it looks like the VBA is just attempting to execute my ShowData query and not run the subquery's which take the date parameters, and is causing the query to have no data to show.

If the sub-queries are in the main query, they have to execute - and get the parameters.

If the queries are in code, you have to have delimiters in the SQL string. When Jet executes a saved query, the delimiters are added as best that Jet can understand. Not so when executing a query in code. YOU have to ensure that the SQL statement is properly formed. I use a LOT of "Debug.Print sSQL " statements in my code. After the queries are executing properly, I comment out the Debug statements.



This is what a query in code with dates would look like in code (my style):

sSQL = "SELECT COUNT(combinedTable.UserID) As CountOfUID"
sSQL = sSQL & " FROM combinedTable"
sSQL = sSQL & " WHERE combinedTable.UserID)= 'RM1219' AND combinedTable.LogInDate Between #" & [forms]![form1]![start] & "# AND #" & [forms]![form1]![end] & "#;"
'     Debug.Print sSQL

OK,
First, notice I removed all of the parenthesis in the WHERE clause. If you look at a saved query in SQL view, Access adds parenthesis... a lot of them. But in code, the parenthesis are unnecessary if all of the connectors are the same: all "ANDs" or all "ORs". (Personal preference)
Second, (in blue) I changed the double quotes to single quotes (for use in code). Or you would have to double up on the double quotes. I can never get the double-double quotes correct.
And third, "Start" and "End" are reserved words in Access and shouldn't be used for object names. And, yes, I know that placing square brackets around the Start/End tells Access they are field/control names, but I don't use reserved words. Period. It is part of my naming convention. That way I never have to worry about strange errors due to use of reserved words.