I've tried searching the Queries forum, but haven't been able to find a thread that addresses my particular conundrum. My apologies if it's out there and I missed it.
I'll try to make this question fall somewhere in between Too Much Detail and Not Enough. I'm working in a database I didn't create. I'm trying to create a Select Query I'll call ExportTrackingData-- based on two existing Queries (that I also didn't create) -- that will return results based on a range of dates entered into a simple Form I've named EnterDates (that I did create).
The existing Queries are named OrdersQuery and TrackingInfo. The relevant Fields I'm working with are OrderNumber, TrackingNumber, and PickupDate. As you might guess, the Criteria for PickupDate corresponds to to the StartDate and EndDate fields in my Form.
It works pretty well, except for two key details. 1) The column in the Table that stores the PickupDate information is formatted as a Text field, not an actual "Date" field. 2) The (text) dates in that field aren't consistently formatted; some of them look like this: 10/25/2012 and some of them look like this: 25-Oct-2012.
I don't know why this is the case -- it just IS.
This means that the Query only returns results where the PickupDate is in the former format, and ignores the ones in the latter format. Not so useful.
I can't change the formatting of the Field in the original Table (again, not my creation). What I'd really like to be possible is for the Query to somehow interpret date data in the 25-Oct-2012 format and convert it to 10/25/2012.
If it helps, here's the Criteria I've written that's returning results for the entries with 10/25/2012-like dates: >=[Forms]![Export Tracking Numbers]![TxtStartDate] And <=[Forms]![Export Tracking Numbers]![TxtEndDate] And Like "*" & Right([Forms]![Export Tracking Numbers]![TxtStartDate],4)
I'm really hoping there's a simple way to make this work, like maybe an additional Criteria or something. I really don't want to have to make my EnterDates form have seperate Start/End boxes for each format.
Thanks in advance,
~Gene
I'll try to make this question fall somewhere in between Too Much Detail and Not Enough. I'm working in a database I didn't create. I'm trying to create a Select Query I'll call ExportTrackingData-- based on two existing Queries (that I also didn't create) -- that will return results based on a range of dates entered into a simple Form I've named EnterDates (that I did create).
The existing Queries are named OrdersQuery and TrackingInfo. The relevant Fields I'm working with are OrderNumber, TrackingNumber, and PickupDate. As you might guess, the Criteria for PickupDate corresponds to to the StartDate and EndDate fields in my Form.
It works pretty well, except for two key details. 1) The column in the Table that stores the PickupDate information is formatted as a Text field, not an actual "Date" field. 2) The (text) dates in that field aren't consistently formatted; some of them look like this: 10/25/2012 and some of them look like this: 25-Oct-2012.
I don't know why this is the case -- it just IS.This means that the Query only returns results where the PickupDate is in the former format, and ignores the ones in the latter format. Not so useful.
I can't change the formatting of the Field in the original Table (again, not my creation). What I'd really like to be possible is for the Query to somehow interpret date data in the 25-Oct-2012 format and convert it to 10/25/2012.
If it helps, here's the Criteria I've written that's returning results for the entries with 10/25/2012-like dates: >=[Forms]![Export Tracking Numbers]![TxtStartDate] And <=[Forms]![Export Tracking Numbers]![TxtEndDate] And Like "*" & Right([Forms]![Export Tracking Numbers]![TxtStartDate],4)
I'm really hoping there's a simple way to make this work, like maybe an additional Criteria or something. I really don't want to have to make my EnterDates form have seperate Start/End boxes for each format.
Thanks in advance,
~Gene
