Using Data in a textbox within a query - formats??

[robjones23]

Hi,

I've got a database which uses two files "Old" and "New". The "Old" file contains data from 2 dates, date1 and date2. The "New" file contains dates from two dates also, however one of the dates is tha same as that in the "Old" File, i.e. date2 and date3

The dates are stored as TEXT, not as Date/Time.

I have a textbox on my form set to Date()-1 which always gives me date2 (The dates within old and new change each day as new files are imported)

Just to explain these files, they're an extract of data between a 24hr period, 7AM - 7AM which explains why they're over two dates. As a new file is created, the previous "New" file gets renamed to "old" so there is always two days of data which goes effectively over 3 dates.

So anyway, my textbox contains the date in the same format as the text field on the table, dd mmm yyyy. Within the criteria section of the query, i have it pointed to the text box on the form. So when the text box = dd mmm yyyy it SHOULD bring out the same date within the "new" and "old" file. I can then combine these two queries into one table with only date2 inside.

However, when I try to use this criteria, no records are returned. I get a feeling this may be because the textbox is a dd mmm yyyy field and the actual table is a TEXT field. Is there a way to make this work??

Thanks!!

Rob.

 

[robjones23]

Thanks Pat,

The tables are already linked, and whenever I try linking them as a date / time field I get a NUM! error. Not sure why but i've managed to make a workaround anyway.

I looked at the "old" file and took the Max of the Date field and put that into a new table with 1 entry. So the new table which is created always has the "reporting date" so to speak....

The queries then run on the "old" and "new" files and merge these into a new table "Current_Date" where they match the date set in the reporting date table. I can then run my figures from that table.

Obviously this creates a bit of bloat but the table is automatically deleted and the database compacted on close so It's not too big an issue.

Cheers,


Rob

 

[robjones23]

It's been giving me the correct figures??

It takes the max from old which would be yesterdays date. And the min from new which is also yesterdays date.