Hi
I am a newbie, both here and to Access. I am a competant Excel VBA programmer but in my new role I am breaking into the brave new world of databases, so please forgive me if I ask what seem like really basic questions.
I recently set up a form in Access to run 5 queries using the same criteria for each of a 'date window'
E.g. If run on 17th Jan the the window would be 20th Jan to 31st Jan.
I did this automatically using a Select Case statement in the Form_Load event on today's date and assigned the correct 'date window' dates to txtStartDate.Value and txtEndDate.Value on the form, which were assigen to each query.
All worked well and I linked the 5 tables to the output spreadsheet, via 'Get External Data' in Excel.
In the 'Workbook_Open' event I then refreshed the data and wrote a macro to manipulate the ouput which worked perfectly. (All good so far!)
It then went Pete Tong when I tried to get clever and create a 6th table containing the txtStartDate.Value and txtEndDate.Value in along with today's date (date run). I wanted to pass this to another Excel sheet in the workbook and use it to create the filename when the SaveAs code was run.
The issue was that the table created was correct with 3 entries:
- Run_Date: Date()
- StartDate: Forms![10 Day Report]!txtStartDate
- EndDate: Forms![10 Day Report]!txtEndDate
but the Excel sheet only had the Run_date. The Start & End dates disapeared.
When I looked at the table in design view, the 2 fields were both 'Binary'. Changing them to text and re-running the query, changed them back to Binary and it still didn't work.
Any ideas? (I bet it is really simple!)
Fritzybabe
(Windows XP, Access and Excel 2003)