Really easy question - I just can't figure it out!

[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]

Yep, create a form to get your input (I guess you could use a VBA "InputBox()") and (in VBA) use TransferSpreadsheet with your query as the argument.

 

[matstars]

Yep, create a form to get your input (I guess you could use a VBA "InputBox()") and (in VBA) use TransferSpreadsheet with your query as the argument.

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)

 

[georgedwilkinson]

Try using all the parameters in your call to TransferSpreadsheet. I know they're optional but how does VB know which one "strSQL" refers to?

 

[matstars]

Try using all the parameters in your call to TransferSpreadsheet. I know they're optional but how does VB know which one "strSQL" refers to?

I really don't know what you are talking about?

I can't just store a query to a string and then have it run the query and output it to excel - seems simple enough? no?

 

[matstars]

Dim varDate As Date
Dim strSQL As String
varDate = Text1
strSQL = "SELECT Employees.Name, Round((((CDate(" & varDate & "))-[IndustryStart])/365),3) AS WorkYears FROM Employees;"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, ?????, "OutputFile.XLS"

What do I put for the ????'s ? do i need to convert my query to a defined table? I'm confused

 

[NigelShaw]

hi,

if the query works fine when you enter the date manually, then set the criteria of the query to the textbox of the form-
Forms![myform]![DateTextBox]
this would be put in the criteria of the date box or whatever is controlling the query

then, set your button to open the report or whatever you view after you enter the date and the query should open dependant on what date was in the box.

NS

 

[georgedwilkinson]

Put strSQL in place of the ???s.