Microsoft Access query question

  The Transporter 13:56 05 Aug 03
Locked

Hi all

I created a database which stores inspections that have been carried on stuff. These inspections are given a date when they have been carried out. i.e 03/06/2003. = 3rd June 2003

The present date is 04/08/2003 = 4th August 2003

I have done a query that will show all inspections done in the last three months.

This is ">Date()-91" but no speech marks.

This query works from the present date.

The new query i would like to create has to do take the data from the inspection 03/06/2003 and then flag it up in say 6 months time that it needs doing again.

I don't know how to get the date 03/06/2003 into an expression.

the expression i would like:

date of tank inspection + amount of days into future > present date

so far i have got

? + 182 > date()

How do i get the ? to be 03/06/2003 but also any other date because i have a few inspections.

:-)

  IanNiblock 14:33 05 Aug 03

You should be able to use DateAdd("d", 182, YourDate)

Where YourDate can be a field name (if this expression is used in a query) or a control (if this expression is used on a form)

If you need any more help let me know

  Mike D 16:02 05 Aug 03

I had a similar problem a few months ago. This not in Access but Excel. I had a list of customers who needed to be contacted every 6 months. this was easy, but pressure of work sometimes meant that a simple "add 6 months to taday's date" did not always work in time, so we needed a flag to highlight the "Make appointmant now" situations.

First of all we set up a cell, for example X1 with a simple formula to return today's date (Today function).

Then another formula is set in a column headed, "Next appointment due". aqssuming you have a column for "last appointment", the formula forthis one is "Sum (A1 + 180) - A1 is the last appt date. Let's call this B1.

In the next column you can make a "MAKE APPOINTMENT" comment appear by using this formula:

=IF(SUM((B1)<($x$1),"Make Appointment"," "

There is a space between the empty quotation marks. Obviously the message could be whatever you want - "Inspection due"???

Mike

  The Transporter 16:14 05 Aug 03

From

DateAdd("d", 182, YourDate)

I typed in

DateAdd("d", 182,"date inspection")


The "date inspection" field's data type is date/time.

it gives the ERROR
Data type mismatch in criteria expression

Does this mean i have mixed up a memmo type with a number type etc.?

What does the "d" mean in the expression.


Also please could you expand on:

"a control (if this expression is used on a form)" I do not understand this bit.

Thanks for your help

:-)

  IanNiblock 16:24 05 Aug 03

Hi,

The "d" is the unit of time that you are adding to the date ("d" = day), there are other values that I cannot recall at the minute.

If you are typing this expression through the query window (i.e. you have a query open in design view) then you should put DateAdd("d", 182, [date inspection]) - this will reference the date inspection field.

By putting "date inspection" instead of [date inspection] the function was trying to convert the string "date inspection" into a date (and obviously failing - resulting in the type mismatch error that you saw).

Hope this helps,

Ian.

PS. The bit about the expression used on a form was relating to the use of VBA code behind an event on an access form.

  The Transporter 16:41 05 Aug 03

I changed the code so that it was

DateAdd("d", 182, [date inspection])

I was able to save it ok but once i click on the query to run it i get this error

--------------------------------------
You tried to execute a query that doesn't include the expression

'[Inspection table].[Type of Inspection]="External" And Last([Inspection table].[date inspection])=DateAdd("d", 182, [date inspection])'

as part of an aggregate function
--------------------------------------------

I tried typing this in but no luck, it then started saying that i had to basically delete "external" etc. It didnt like it.

The "Inspection table" is the name of the table which the date is stored on in a field called "date inspection".

The last is to show the last inspection done for 1 thing because there are more than 1 inspections done for some things.

The name of the query is "Last External visual inspection query"

The "External" part is where it only finds external inspection as there are also "internal", which will be in another seperate query. It is a field called "type of inspection" in the "Inspection table" table. :-)

I will be off the web now till tomorrow morning but thanks for your help and the explanations so far.

  IanNiblock 08:27 06 Aug 03

Can you post the entire SQL of your query so that I can get a better idea of exactly where the problem lies?

  The Transporter 10:00 06 Aug 03

put in above. Is that what you mean?

DateAdd("d", 182, [date inspection])

  IanNiblock 10:16 06 Aug 03

No - open the query in design view, select the SQL view from the top left corner and copy/paste what you see.

  The Transporter 10:23 06 Aug 03

SELECT DISTINCTROW [Risk table].[Class number], [Risk table].[Risk Classification], [Tank data table].[Risk Classification], [Inspection table].[Tank No Id], [Inspection table].[Type of inspection], Last([Inspection table].[date inspection]) AS [LastOfdate inspection]
FROM ([Tank data table] LEFT JOIN [Risk table] ON [Tank data table].[Risk Classification] = [Risk table].[Risk Classification]) LEFT JOIN [Inspection table] ON [Tank data table].[Tank No] = [Inspection table].[Tank No Id]
GROUP BY [Risk table].[Class number], [Risk table].[Risk Classification], [Tank data table].[Risk Classification], [Inspection table].[Tank No Id], [Inspection table].[Type of inspection]
HAVING ((([Inspection table].[Type of inspection])="External") AND ((Last([Inspection table].[date inspection]))=DateAdd("d",182,[date inspection])))
ORDER BY [Inspection table].[Tank No Id], Last([Inspection table].[date inspection]) DESC;

  IanNiblock 10:37 06 Aug 03

Try the following - I do not have access on my machine here at work so I cannot even tell you if the syntax is correct!! IF it doesn't work then let me know what the error is and I will do my best to sort it out :-)


SELECT DISTINCTROW [Risk table].[Class number], [Risk table].[Risk Classification], [Tank data table].[Risk Classification], [Inspection table].[Tank No Id], [Inspection table].[Type of inspection], Last([Inspection table].[date inspection]) AS [LastOfdate inspection] FROM ([Tank data table] LEFT JOIN [Risk table] ON [Tank data table].[Risk Classification] = [Risk table].[Risk Classification]) LEFT JOIN [Inspection table] ON [Tank data table].[Tank No] = [Inspection table].[Tank No Id] GROUP BY [Risk table].[Class number], [Risk table].[Risk Classification], [Tank data table].[Risk Classification], [Inspection table].[Tank No Id], [Inspection table].[Type of inspection] WHERE ((([Inspection table].[Type of inspection])="External") AND (DateAdd("d",182,(Last([Inspection table].[date inspection])))<=Date)) ORDER BY [Inspection table].[Tank No Id], Last([Inspection table].[date inspection]) DESC

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…