SQL Select query output to Access or Excel? (1 Viewer)

[matstars]

I have a query with that has a variable (it’s a date – more on this later)



The table that I am getting the info from is called Employee, it is flat it just has “Employee Name”, “StartDate.” It has about 50 records in the table. I’m doing this to learn Access’ logic before I tackle bigger projects. OK on a form I want to have the user enter in a date and it output the workers who started before that date.



So I wrote up a query in access' design view that works fine if I input the date (it gives you an inputbox when I run the query from access.)


I want to create a form that has a text box where the user enters the date and it processes the query with that date (instead coming from the input box) – and a button to run the query.


I’d like access to either output it as a query (normally in access) or output it to excel if possible.


By the way I'm not worried with verification (that it's a date - there's an IsDate function built into access' vba that I'll use).



Also I have the SQL from the query saved - it's just

SQL_Query= "Select * from Employee where ((Round((((CDate(" & X & "))-[StartDate])/365),3))>0)"

Where X would be the date. Is there any way to easily do this?


Does my question make sense?



Thanks!



Mat

 

[georgedwilkinson]

I coulda sworn I answered this earlier, but apparently I didn't hit Post.

If you just want to use VBA with nothing else involved, just get your variable from InputBox() and output the newly formed query via TransferSpreadsheet().

 

[matstars]

Doesn't seem to work?

Dim varDate As Date
Dim strSQL As String
varDate = Text1
strSQL = "SELECT Employees.Name, Round((((CDate(" & varDate & "))-[IndustryStart])/365),3) AS WorkYears FROM Employees"
DoComd.TransferSpreadsheet (strSQL)


is my code, Text1 is a textbox with 1/1/2000 in it

I get typemismatch on DoCmd.TransferSpreadsheet (strSQL)

 

[pdx_man]

Put a Breakpoint on the Docmd line. Go into the <b>Immediate</b> window <i> Ctrl G </i> and type in:
? strSQL

Hit enter. That will return what is in the variable. Copy this to a query.
Query Window <New> <OK> <Close> <First button on toolbar SQL> <Paste> <!>

Debug that and correct your VBA statement