Date/time question. (1 Viewer)

LisaJill · Jun 7, 2003

Greetings,

I need some help with a date function, if anyone is able.

I have a database, and held within its contents is a list of contest winners. One of the rules however is that one person can not win more than one contest every 60 days. Unfortunately, people still enter

To this end, I've been asked to create a report listing the names of all winners within the last 60 days.

There is really only one table, and it has a bunch of information including first name, last name, address etc etc, and a Date Won field - which is where the date that contest was won, by that particular person, is entered.

There is an existing query there and they've added the DateWon field and put in

Date() and Date()-60

This brings up 0 records. Now I am 100% positive there are atleast 5 records of winners in the last 60 days (quite a bit more than that).

Can someone assist me? This seems like it should be a fairly simple issue.

Also, once I have the basic report - is it possible to use the same restrictions and to have a second query whereby I can have it search for a Last Name (which is a seperate field) within the restricted 60 day query? For a fast lookup to see if a particular person won in the last 60 days, this would be very nice.

Thank you everyone for your assistance.

-Lisa

Rich · Jun 7, 2003

>=Date()-60
or >=DateAdd("d",-60,Date())

LisaJill · Jun 7, 2003

Thank you! =)

LisaJill · Jun 13, 2003

I tried both of these, as well as using Between...

Both of these give me 4096 returns.. which is every return, including dates from the year 2000....

The date field in the table is ShortDate... an other ideas how to fix this?

thanks!

pcEars · Jun 13, 2003

Try using 2 tables:

tblWinners
WinnerID [autonumber, PK]
FirstName [text]
LastName [text]
(Other fields as needed)

tblContests
ContestID [autonumber, PK]
ContestDate [date/time]
WinnerID [lookup field]
(Other fields as needed)

Access should set the relationship as 1:many between them on the WinnerID field.

Then use the query wizard to start a new FindDuplicates query. Use a duplicate value field that will be unique from winner to winner, such as Social Security Number [I would think that you could, or must get that from the winner for tax reasons]

Don't forget to use the >=Date()-60 as criteria for your date field in the query.

LisaJill · Jun 13, 2003

Thank you for the suggestion. I mayh compress it and bring it home and attempt that. However given the amount of time there etc I'm not sure this will really be a viable solution.

There is no way to do it short of overhauling the database? They don't even track the name of the contest that much, really, its just a drop down box... I guess I could expand that table tho.

Actually, if I have some free time I may move the db over and do just that....

I'm just worried that it still won't work, given that it doesn't work in the first place. Why would adding a table force this function to work?

thanks for the idea =)

pcEars · Jun 15, 2003

Why would adding a table force this function to work?

You know, maybe it won't matter. If I've spoken out of turn, I apologize.

As a relative newbie, I often use the wizards to accomplish my goals in Access. Perhaps the FindDuplicates will get you there without the table splitting.

Also, there is a wizard for splitting the tables, should you decide to go that way.

Whatever you do, make a copy of your .mdb and test any changes on it.

Pat Hartman · Jun 15, 2003

Both your original between Date() and Date()-60 and the suggested >= Date()-60 should have worked. Post the entire SQL statement so we can see what is going on.

LisaJill · Jun 15, 2003

Thats all I'm doing.

I have a query that has FirstName, LastName, DateWon and PrizeWon

In the query designer, in DateWon, I'm cut and pasting the suggested entries into the Criteria for DateWon

I then preview - and get all records.....

I'm not using any code or SQL - what shows up in 'expression builder' is exactly whats posted here....

sorry i'm not great at code, if theres more info I can provide let me know and I will =)

LisaJill · Jun 15, 2003

pcEars said:
You know, maybe it won't matter. If I've spoken out of turn, I apologize.

Oh there's no need! I'm just trying to understand why that would help to make it work - since its a lot more work. This is an internship and it's an added thing i took on my own =)

If it'll make it work I'll do it - I just don't see the logic as to why it would!

Pat Hartman · Jun 15, 2003

Open the query in design view, change the view (leftmost button on the button bar) to SQL. This view shows the SQL string that the query builder built for you. Copy and paste the SQL string here.

LisaJill · Jun 19, 2003

SQL

As requested, this is what it's putting in:

For: >= Date()-60

SELECT [Listener Database Master].FirstName, [Listener Database Master].LastName, [Listener Database Master].Address, [Listener Database Master].City, [Listener Database Master].State, [Listener Database Master].PostalCode, [Listener Database Master].HomePhone, [Listener Database Master].Birthdate, [Listener Database Master].[Date Won], [Listener Database Master].Contest, [Listener Database Master].[Prize Won]
FROM [Listener Database Master]
WHERE ((([Listener Database Master].[Date Won])>=Date()-60));

and for
between Date() and Date()-60

SELECT [Listener Database Master].FirstName, [Listener Database Master].LastName, [Listener Database Master].Address, [Listener Database Master].City, [Listener Database Master].State, [Listener Database Master].PostalCode, [Listener Database Master].HomePhone, [Listener Database Master].Birthdate, [Listener Database Master].[Date Won], [Listener Database Master].Contest, [Listener Database Master].[Prize Won]
FROM [Listener Database Master]
WHERE ((([Listener Database Master].[Date Won]) Between Date() And Date()-60));

Thank you for any further help you can provide - I finally sent a copy of this database home so I can respond to questions far more quickly!

Jon K · Jun 20, 2003

In table design, check the data type of the Date Won field.

If it is Text, add a new Date/Time field in the table and use an update query to update the new field:-
UPDATE [Listener Database Master] SET [Listener Database Master].NewDateWon = CDate([Date Won]);

Check to see if the conversion is correct as different country may have different position for month and day in a Date/Time field.

You can then delete the old field and rename the new field in table design.

LisaJill · Jun 20, 2003

Date Won is:

Date/Time
Format: Short Date
Input Mask: 99/99/00;0

Anything else I can check?

LisaJill · Jun 20, 2003

Could I be having problems due to Date Won actually being:

field name: Date Won

is the space an issue?

LisaJill · Jun 20, 2003

Well, the problem I was having with this was not the database.

The PC reverted to 1999, if you can believe it. I'm still trying to troubleshoot exactly -why-.

Anyhow, when it was set to the current date, the query (in all it's forms) worked beautifully.

Sorry to waste everyone's time. Thank you all so much for the help!

Date/time question. (1 Viewer)

LisaJill

Registered User.

Rich

Guest

LisaJill

Registered User.

LisaJill

Registered User.

pcEars

qryNot_Quite_StoopID

LisaJill

Registered User.

pcEars

qryNot_Quite_StoopID

Pat Hartman

Super Moderator

LisaJill

Registered User.

LisaJill

Registered User.

Pat Hartman

Super Moderator

LisaJill

Registered User.

Jon K

Registered User.

LisaJill

Registered User.

LisaJill

Registered User.

LisaJill

Registered User.

Similar threads

Users who are viewing this thread