Dates - Mixed format

[copaaccess]

I am using MS access via OBDC to a DB.

A column in a table contains dates, however they are in the format DD/MM/YYYY and DD/MM/YYYY HH:MM:SS.

I have a query (main) that calls other queries (sub), and the Main query requires the user to input a From and To date.

The results of the query exclude the records that have HH:MM:SS for the To date.

How can I ensure I retrieve the missing dates?

I have tried converting the date to text by the of the Day, Moth, Year function, and then converting it back with DateValue. ie

td: DateValue(Day([add_date]) & "/" & Month([add_date]) & "/" & Year([add_date]))

If I place the date range in the criteria field ie "between #01/01/2012# and #05/01/2012#", the query works fine and the results back are as expected. If I change the criteria to "between [From] and [To]" and then run the query and I put in 01/01/2012 for From and 05/01/2012 for To, I get all records with a value of DD equal to 01, 02, 03, 04, 05, and MM and YYYY every combination that is in the data ie 01,02,03,04,05,06,07,08,09,11,12 and 2012, 2011, 2010 etc

All the queries are built in the Design section and not in VB

I am at wits end

Thanks

 

[Galaxiom]

Converting to text is entirely the wrong direction to take.

The To date needs have the time component 23:59:59 added so that times during that day are included.

Without knowing exactly how you have constructed the query I cannot tell you exactly how to impliment this.

 

[Galaxiom]

Some posters may suggest stripping the time from the records in the query match but this is not a the best way because it requires a function being applied to each record. Adding the time component to the criteria is considerably faster.

 

[copaaccess]

1. I understand adding time could and should resolve this.How would you add the time to only those records where there is no time. Remeber the original value is a is a Date/Time type field
2. Making suer add Time component to the user input criteria - Users are not that clever!

 

[copaaccess]

Here is the SQL ( I use the design window) of the main query

SELECT qry_KevinN_Logistics_report.add_date, qry_KevinN_Logistics_report.scale_ticket_id, qry_KevinN_Logistics_report.plc_id, qry_KevinN_Logistics_report.ship_date, qry_KevinN_Logistics_report.vendor_id, qry_KevinN_Logistics_report.full_name, qry_KevinN_Logistics_report.commodity_id, qry_KevinN_Logistics_report.ship_mode_code, qry_KevinN_Logistics_report.train_barge, qry_KevinN_Logistics_report.scale_ticket_inbound_outbound, qry_KevinN_Logistics_report.contract_id, qry_KevinN_Logistics_report.add_by, qry_KevinN_Logistics_report.quantity, qry_KevinN_Logistics_report_Storage_site.contract_id AS [TS contract], qry_KevinN_Logistics_report_Storage_site.vendor_id AS [Site ID], qry_KevinN_Logistics_report_Storage_site.full_name AS [Site Name], dba_s1_commodity.brand_code AS DESK
FROM (qry_KevinN_Logistics_report LEFT JOIN qry_KevinN_Logistics_report_Storage_site ON (qry_KevinN_Logistics_report.ship_mode_code = qry_KevinN_Logistics_report_Storage_site.ship_mode_code) AND (qry_KevinN_Logistics_report.scale_ticket_id = qry_KevinN_Logistics_report_Storage_site.scale_ticket_id)) LEFT JOIN dba_s1_commodity ON qry_KevinN_Logistics_report.commodity_id = dba_s1_commodity.commodity_id
WHERE (((qry_KevinN_Logistics_report.add_date) Between [From Date] And [To Date]) AND ((qry_KevinN_Logistics_report.ship_mode_code)="EXP") AND ((qry_KevinN_Logistics_report.train_barge) Like "bulk*" Or (qry_KevinN_Logistics_report.train_barge) Like "vessel*") AND ((qry_KevinN_Logistics_report.scale_ticket_inbound_outbound)="o"));


Here is the SQL of the 2 sub queries
qry_KevinN_Logistics_report

SELECT dba_s1_quantity_entry.scale_ticket_id, dba_s1_quantity_entry.plc_id, dba_s1_quantity_entry.ship_date, dba_s1_quantity_entry.vendor_id, dba_s1_name_and_address.full_name, dba_s1_quantity_entry.commodity_id, dba_s1_quantity_entry.iv_product, dba_s1_quantity_entry.ship_mode_code, dba_s1_quantity_entry.train_barge, Round(IIf([applied_type]="R",[applied_report_quantity]*453600/1000000000,[applied_scale_quantity]*453600/1000000000),2) AS quantity, dba_s1_name_and_address.offsite_storage_flag, dba_s1_quantity_entry.scale_ticket_inbound_outbound, dba_s1_contract.contract_id, dba_s1_quantity_entry.add_by, dba_s1_quantity_entry.add_date
FROM ((dba_s1_quantity_entry INNER JOIN dba_s1_vehicle_processing ON dba_s1_quantity_entry.quantity_entry_nbr = dba_s1_vehicle_processing.quantity_entry_nbr) INNER JOIN dba_s1_name_and_address ON dba_s1_quantity_entry.vendor_id = dba_s1_name_and_address.name_and_address_id) INNER JOIN dba_s1_contract ON dba_s1_vehicle_processing.contract_nbr = dba_s1_contract.contract_nbr
WHERE (((dba_s1_quantity_entry.plc_id)="Cash" Or (dba_s1_quantity_entry.plc_id)="AGA" Or (dba_s1_quantity_entry.plc_id)="ARMS" Or (dba_s1_quantity_entry.plc_id)="EXPORT") AND ((dba_s1_name_and_address.offsite_storage_flag)="N") AND ((dba_s1_quantity_entry.scale_ticket_void_flag)<>"y"));

qry_KevinN_Logistics_report_Storage_site
SELECT dba_s1_quantity_entry.scale_ticket_id, dba_s1_quantity_entry.plc_id, dba_s1_quantity_entry.ship_date, dba_s1_quantity_entry.vendor_id, dba_s1_name_and_address.full_name, dba_s1_quantity_entry.commodity_id, dba_s1_quantity_entry.iv_product, dba_s1_quantity_entry.ship_mode_code, dba_s1_quantity_entry.train_barge, Round(IIf([applied_type]="R",[applied_report_quantity]*453600/1000000000,[applied_scale_quantity]*453600/1000000000),2) AS quantity, dba_s1_name_and_address.offsite_storage_flag, dba_s1_quantity_entry.scale_ticket_inbound_outbound, dba_s1_contract.contract_id, dba_s1_quantity_entry.orderlog_nbr, dba_s1_quantity_entry.add_by, dba_s1_quantity_entry.add_date, dba_s1_name_and_address.state_province_code
FROM ((dba_s1_quantity_entry INNER JOIN dba_s1_vehicle_processing ON dba_s1_quantity_entry.quantity_entry_nbr = dba_s1_vehicle_processing.quantity_entry_nbr) INNER JOIN dba_s1_name_and_address ON dba_s1_quantity_entry.vendor_id = dba_s1_name_and_address.name_and_address_id) INNER JOIN dba_s1_contract ON dba_s1_vehicle_processing.contract_nbr = dba_s1_contract.contract_nbr
WHERE (((dba_s1_quantity_entry.plc_id)="Cash" Or (dba_s1_quantity_entry.plc_id)="AGA" Or (dba_s1_quantity_entry.plc_id)="ARMS" Or (dba_s1_quantity_entry.plc_id)="EXPORT") AND ((dba_s1_quantity_entry.ship_date)>#10/1/2008#) AND ((dba_s1_name_and_address.offsite_storage_flag)="Y") AND ((dba_s1_quantity_entry.scale_ticket_void_flag)<>"y"));

 

[Galaxiom]

Don't change the records. Configure the query such that the To Date includes 23:59:59

There are a number of ways to do this addition. This one adds a day and subtracts a second. Put it in the criteria on the date field of the query.

Between [Enter From Date] And DateAdd("s", -1, [Enter To Date] + 1)

 

[speakers_86]

This is why I don't like storing the date and time together. Plus, I have never seen a date picker that includes time. Also, they define the same thing, but are conceptually different. If you want to check the time, you don't look at the calendar!

This could probably consume an entire thread I think.

 

[Galaxiom]

One good reason to keep date and time apart is when showing records by date in a subform. The LinkChildField with a Date & Time won't match Date only in the LinkMasterField.

 

[copaaccess]

Galaxiom
I added you expression to the criteria but got the following after entering the dates
"the expression is typed incorrectly, or is too complicated to be evaulated"

Any other clues?

 

[Galaxiom]

It didn't like the simple arithmetic addition of the day. It does work in most places but apparently not there.

Use this:

Between [Enter From Date] And DateAdd("s",-1,DateAdd("d",1,[Enter To Date]))

 

[copaaccess]

Thanks for that. It worked. Greatly appreciated