Due Date Form question

[AcmeGearSteve]

Hey there, got a new question about Access 2007.

I've been asked to start a new database for tool calibration records. I've entered all my information in two tables (Tool Calibrations, Calibration Certs); the one I'm more concerned with is the Tool Calibration table and form.



My fields are:
- Serial Number
- Calibration Date
- Next Calibration Date
- Tool Type
- Tool Range

I want to make a form that will either:
1. Possibly entering a date (or date range) and tell me which tools will be up for "Next Calibration Date" on that day (ex. [we use 3-month calibration schedules] if I put in "9/29/10", it would return, with the field from "Next Calibration Date" the 12/29/10 tools).
2. List all possible tools needed form calibration in the following week (would be ideal, but I doubt possible).

I know a query is needed, but what I can't figure out is how to specifically display the results on a relevant form. Once again, my VBA is lacking, so I figure there is a code I can use, but I don't know it.

Thanks in advance,
Acme Gear Steve

[pbaldy]

If it's always 3 months, there's really no need to save the next date; it can be calculated from the calibration date. If you still want to do it, in the after update event of the calibration date field:

Me.NextCalibrationDate = DateAdd("m", 3, Me.CalibrationDate)

Listing the upcoming items is fairly easy. A query with a criteria like:

Between OneDate And AnotherDate

would do it, and you can use functions from here to automatically get the following week:

http://www.pacificdb.com.au/Support/...kfunctions.htm

[slave138]

As for your number two request, I would probably create a tool table with a Yes/No field for each calibration test. Then you could just query the table for any true values for a given test.

I'm sure there are other ways it can be done depending on how everything is setup.

[AcmeGearSteve]

If it's always 3 months, there's really no need to save the next date; it can be calculated from the calibration date. If you still want to do it, in the after update event of the calibration date field:

Me.NextCalibrationDate = DateAdd("m", 3, Me.CalibrationDate)

Listing the upcoming items is fairly easy. A query with a criteria like:

Between OneDate And AnotherDate

would do it, and you can use functions from here to automatically get the following week:

http://www.pacificdb.com.au/Support/...kfunctions.htm

Thanks for the response pbaldy, after thinking about it, I think you may be right. Since we do use 3-month calibration periods, there's no need to make it more complicated than it needs to be.

So for this

Me.NextCalibrationDate = DateAdd("m", 3, Me.CalibrationDate)

I just tacked it onto a RowSource for a ComboBox, making it:

SELECT [ToolCalibration Query].CalibrationDate FROM [ToolCalibration Query] 
ORDER BY [ToolCalibration Query].CalibrationDate, [CalibrationDate] 
AND DateAdd("m",3,CalibrationDate);

This works, and it prompts me for a "CalibrationDate", but when I put in the Cali-Date it just gives me a drop-down box of all the Current Calibration Dates.

[pbaldy]

You need a criteria as I mentioned. How about this untested SQL:

SELECT DateAdd("d",90,CalibrationDate) FROM [ToolCalibration Query]
WHERE DateAdd("d",90,CalibrationDate) Between (Date() + (7 - Weekday(Date() , 2)) + 1) And (Date() + (7 - Weekday(Date() , 2)) + 7)
ORDER BY DateAdd("d",90,CalibrationDate)

I assume you'd want to add a field to identify the unit.

[AcmeGearSteve]

You need a criteria as I mentioned. How about this untested SQL:

SELECT DateAdd("d",90,CalibrationDate) FROM [ToolCalibration Query]
WHERE DateAdd("d",90,CalibrationDate) Between (Date() + (7 - Weekday(Date() , 2)) + 1) And (Date() + (7 - Weekday(Date() , 2)) + 7)
ORDER BY DateAdd("d",90,CalibrationDate)

I assume you'd want to add a field to identify the unit.

So, I dropped that code into an SQL Query, and at first I got a "Circular Reference", I figured it was because the name of the Query was in the FROM line, so I changed it to "FROM [ToolCalibration]" (I tried to make it [ToolCalibration Table], but it didn't like that much, so I assume ToolCalibration by itself assumes a table unless otherwise specified).

When I open the query now, the only field I get is "Expr1000", but I don't even see that in the coding itself, so I couldn't figure out where it was from.

[pbaldy]

That's a default alias. If you go back into SQL view, you'll see:

SELECT DateAdd("d",90,CalibrationDate) AS Expr1000 FROM

In design view you'd see

Expr1000: DateAdd("d",90,CalibrationDate)

In either place you can change that to a description with more meaning to you, like ExpireDate.

[AcmeGearSteve]

Alright, thanks for that.

I've messed around with it and got to:

SELECT ToolCalibration.[DateAdd("d",90,CalibrationDate)] AS CurrentCalibration, DateAdd("d",90,[CalibrationDate]) AS NextCalibration
FROM ToolCalibration
WHERE (((DateAdd("d",90,[CalibrationDate])) Between (Date()+(7-Weekday(Date(),2))+1) And (Date()+(7-Weekday(Date(),2))+7)))
ORDER BY ToolCalibration.[DateAdd("d",90,CalibrationDate)];

There are now two issues I'm having. 1. I get a pop-up requirement first for "ToolCalibration.DateAdd("d",CalibrationDate)" . I feel like it's not picking up the data from the Table. And 2. I can't type anything in my CurrentCalibration Box, it says it's based on an expression and can't be edited.

[pbaldy]

It is an expression, due to the DateAdd() function:

ToolCalibration.[DateAdd("d",90,CalibrationDate)] AS CurrentCalibration

The popup is because with the table name at the beginning, it thinks you're telling it that is a field in the table, which it can't find. I think you want:

ToolCalibration.CalibrationDate AS CurrentCalibration

[AcmeGearSteve]

It is an expression, due to the DateAdd() function:

ToolCalibration.[DateAdd("d",90,CalibrationDate)] AS CurrentCalibration

The popup is because with the table name at the beginning, it thinks you're telling it that is a field in the table, which it can't find. I think you want:

ToolCalibration.CalibrationDate AS CurrentCalibration

Alright, thanks for the help. I'll try and figure it out from here; don't want to take up all your time and whatnot.

Thanks again!

[pbaldy]

No problem, and post back if you get stuck. The goal is to throw you into the pond and let you learn to swim, but not drown you.