Querry: difficult one

[gianina]

Hi All,

I want to create a querry and I don't know to do it to show what I want.

Some explanations:

The scope of my DataBase is to permit users to enter new NCR's (non conformance reports) and also update them.
For open date I created 3 fields:
1. Opening Day
2. Opening Month
3. Opening Year

Our management decided that any NCR's opened MUST be closed in 10 days from day of opening.

I want to create a querry called "Past due".
Since the reference field is "Opening day" which is a "Number" field I don't know how to create this querry.

I know that there's must be a calculation like:

"Opening day" + 10 days

but, the thing is that my "Opening day" field will have numbers from 1 to 31 and the calculation from above doesn't make sense.

Any ideas ?

Thanks.

 

[PHV]

Why THREE fields for a SINGLE date value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rjoubert]

Add this expression to your query...

CDate([NCRTable]![OpeningMonth] & "/" & [NCRTable]![OpeningDay] & "/" & [NCRTable]![OpeningYear]) + 10

 

[gianina]

rjoubert,

As criteria under field "opening Day" ???

What fields should the query contain ?

Thanks.

 

[rjoubert]

OK, use this as the SQL behind your Query...

SELECT Date()-(CDate([NCRTable]![OpeningMonth] & "/" & [NCRTable]![OpeningDay] & "/" & [NCRTable]![OpeningYear])) AS DaysOld
FROM NCRTable
WHERE (((Date()-(CDate([NCRTable]![OpeningMonth] & "/" & [NCRTable]![OpeningDay] & "/" & [NCRTable]![OpeningYear])))>=10));

 

[gianina]

rjoubert,

I did it and I have the following message:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"

I am completely lost...

 

[rjoubert]

Your life would be so much easier if you had the date in one field in your table, instead of being split up into three fields.

 

[rjoubert]

Do all of the records in your table have valid entries in your OpeningMonth, OpeningDay, and OpeningYear fields?

 

[gianina]

rjoubert,

Your questions regarding the date in one field....

That was my original plan but I had to create a query which would show all opened NCR's from a certain MONTH, and if I had the day; month and year in one field I could do it.
What I wanted to happen is on runing the query a pop-up window whould ask the user to enter the month...but when the user eneters a certain month the query cannot run because that field contains also day and year too, not only month. That's why I decided to split in 3 different fields.

Regarding your second question....yes, all records in my table have valid entries in those 3 fields.

 

[PHV]

With a single date field you may EASILY get the Day, the Month or the Year value with the corresponding function, eg:
WHERE Month([the date field]) = [Please enter month (1-12)]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rjoubert]

If you had one date field, you could use the following expression in your query to get a particular month...

DatePart("m",

![DateField])

 

[gianina]

Right now, I'm too far with the project to be able to modify the date field.
I am about to present my project for release purposes and changing the data field into one date field would require a lot of work (ex: modifying querries, reports, forms) and unfortunately I do not have the necessary time.

Any help is appreciated.

Thanks.