Make Table and Date Query

[asp_learner]

Hi,

I am having trouble in the following query
to make the date query a date. That is because I am doing different things, I am selecting first date as date1 and Last Date as Date 2. If the second date equals first date,it is null. Doing that makes the field a text field.

Do you have any suggestions?

SELECT future2002.[Store_ No] AS Store, First(future2002.Date) AS First, Max(future2002.Date) AS Last, IIf([Last]=[First],"",[Last]) AS Second INTO [Future Dates]
FROM future2002
GROUP BY future2002.[Store_ No];

 

[asp_learner]

actually is there a better way I could have done that query? Can I do first date, second date and third date

Thanks
Eva

 

[RV]

Eva,

a few remarks.
First, don't use Date as a column name in a table. It's a reserved word in Access.
Second, the function First doesn't give the first value for your datecolumn unless you use an Order By clause.If you want to substract the "oldest" date, better use the Min function.
Third:

>If the second date equals first date,it is null.<

In your IIF function you use "".That means empty which isn't the same as Null. I guess you mean empty (blank).

>actually is there a better way I could have done that query? Can I do first date, second date and third date<

What do you want to establish?
What is the purpose of your first, second and third date?
What do you need the new table for?

Greetings,

RV

 

[asp_learner]

actually the date field is in somebody else's table so I can't change that.

I basically once the first, second and third dates for a store to be inventoried. Her table has a list of all the dates for stores.

Is there a way to do first, second and third

 

[RV]

>Is there a way to do first, second and third<

Perhaps I wasn't too clear with my former question. What do you mean by first, second and third?

>actually the date field is in somebody else's table so I can't change that.<

Let it get changed rightaway, otherwise you don't have any guarantee you're going to get the results you're looking for!!!

RV