comparing dates

[beanbeanbean]

hi is there any way to make a query in which i am able to compare 3 dates ?


for example

i have a date field called creation date. this date must be earlier than a date field called shipping date. and i have a date field called close date. this date must be later than the field called shipping date.

so in a sense creation date < shipping date < close date



is there a query that i can create so that i can extract the dates quickly into another table with all that requirements and thus not going through them 1 by 1 in the table ?


thanks so much. god bless.

 

[dickohead]

Create a query that selects all three dates for some ID column.

So you would get a list like:

1 01/01/01 02/02/02 03/03/03
2 02/02/02 01/01/01 03/03/03

Then you'd want to filter it to select item 2 yes?

I'd select the coumns ID, CreationDate, ShippingDate and CloseDate
Then in the 'criteria' section of your ID column for the query I'd write something like:
[CreationDate] > [ShippingDate]

Then one line after that I'd write:
[ShippingDate] > [CloseDate]

That would then select any ID's where the CreationDate is later in time than the ShippingDate OR where the ShippingDate is later than the CloseDate.

I think...

I tested it one of my databases and it worked great.

 

[beanbeanbean]

hey thanks man ! but what if i want to compare 2 fields in 2 different tables.

like shipping is from one table and creation date is from another table. how do i phrase the starting of the query ? sorry i'm kinda new to queries.

 

[DCrake]

When using multiple fields with multiple conditions it makes sense to write a fuction to handle this. such as

Aircode (Not tested)

Function Wanted(Optional Created As Date,Optional Shipped As Date, Optional Closed As Date) As Boolean
'Have included the Optional argument incase any of the fields are empty

Dim bFlag As Boolean

If Shipped > Created And Closed > Shipped Then
   bFlag = True
Else
   bFlag = False
Endif

Wanted = bFlag

End Function

Then in your query call the function in a new column.

Required:Wanted([Created Date],[Shipped Date],[Closed Date])

this will give you a True/False flag as to the validity of the data. You can then filter on the True records only.

 

[beanbeanbean]

hey dcrake thanks. i'll try it out. i forgot to ask. is there a way to add the tables ?

for example, i have a table which have 2 fields.

one field contains the data S8977660H and the other field contains the data THD

and then in another table, i have a field which is S8977660HTHD

and if i extract them both into the same table is there a query that can match and compare both of them ?

so that if they are the same that line of data would appear.

i've been trying it out for some time but could not get it.


thanks. god bless.

 

[DCrake]

Ok

Create a new query and include your first table with the two fields

In your first empy column type in TwoIntoOne:[FieldOne] & [FieldTwo]

Where FieldOne And FieldTwo are the actual field names.

Next save this query with a meaningful name

Next use the matched and unmatched query wizards to compare the query to the table.

David

 

[beanbeanbean]

i'm so sorry Dcrake.

"Create a new query and include your first table with the two fields

In your first empy column type in TwoIntoOne:[FieldOne] & [FieldTwo]"

i don't quite get this part. would u mind rephrasing it ? thanks

 

[beanbeanbean]

well i kinda solved the combining part.

i changed the code to yours. it now looks somthing like this:

SELECT [test1].[Opty Id], [test1].[Opty Name], [SJ_CIS].[NEWCINSFX], [test1].[Contact CIN] & [test1].[Contact CIN SFX], [test1].[Create By (Name)], [test1].[Created By (Login)], [test1].[Opty Created Date], [SJ_CIS].[JOIN_DATE], [test1].[Opty Closed Date], [test1].[Closed by (Emp #)], [test1].[Referral - Employee #], [test1].[Referral - Employee], [test1].[Sales Rep], [test1].Product INTO test3
FROM test1, SJ_CIS;


but there's a funny problem. i went to the datasheet view to have a preview look, microsoft access named that table expre1003, is there a way that i can customize the name of that part of the table ?

 

[DCrake]

Where it gives you the expr003 simply overtype this with a meaningful name

 

[vandy05]

Where it gives you the expr003 simply overtype this with a meaningful name

I have a question.
I have a table that has a field date issued and one date mailed.
Of course date mailed will always be greater than or equal to date issued.
I want to put a validation into the table (I did that so certain fields cannot be left empty) but i get an error about not being able to compare columns like that.
Then I came across this - and I tried teh query but now I am confused.
I just basically want an error message displayed if the date mailed is less than the date issued.
Is this possible?

 

[Banana]

In order to validate like that, you would use a form with the required textboxes to display the DateIssued and DateMailed. You then can use the textbox's Validation property which is more flexible than table's Validation property or use its BeforeUpdate event to check if it's not prior to DateMailed.

 

[vandy05]

Thank you so much!!! It works!

 

[Banana]

Excellent. Thanks for reporting success.