Different date format relationships

[CCFL]

I know you can build a relationship between 2 different tables using a date field. But is there a way to build it when; table1.balancedate uses mm/dd/yyyy and table2.dropamount uses mm/dd/yyyy hh:mm:ss.

I am open to all suggestions. Thanks in advance.

 

[namliam]

They are one and the same, atleast assuming they are real date fields...
and maybe assuming the hh:mm:ss is 00:00:00

If not you can use Left or Format to make in a query a "new" field that has the right format then use the query to join to the other table.

 

[CCFL]

Thanks Namliam,

I tried a straight relationship and the query came back with no data. I don't understand your other options.

 

[namliam]

OK, lets start at the base of it all, what is the format both of your fields? Text or Date?

The one with time is ....
The one without time is ...

 

[Brianwarnock]

From post #1 we can assume that they are date/time fields and we must assume that you are happy to select on date only, forget a join on dates and create a dateonly field in table2 using Cdate(Format(yourfield,"mm/dd/yyyy")) and check for equality with the field in Table1.

Brian

 

[CCFL]

Table 1: BalanceDate (mm/dd/yyy format) and ServiceSales
Table 2: DropDate (mm/dd/yyyy hh:mm:ss) and DropAmount

I would like to report for a Date, ServiceSale, and DropAmount in one report. I get the date, ServiceSales then #DELETE or a blank screen in database view. I originally figured that I could simple join BalanceDate and DropDate, that would match date info and product the report that I needed.

Thanks for Nailiam and Brianwarnock. Cause this is driving me incane. It should be something simple and it not. ahhhhhh...just fustrated

Thanks

 

[Brianwarnock]

As I attempted to say your query should have in its where clause
Cdate(Format([dropdate],"mm/dd/yyyy")) =[balancedate]

Brian