Creating a query based on info calculated in a form 1

[Powerhouse123]

Hello,

I have a form which is calculating information and I want to produce a query based on the calculated information on the form. I realise you cannot create a query based on a form...or can you?

My base table includes the following:

Issue Date: 01/01/2005

Expiry Date: Nothing - this is calculated in the form (=DateSerial(Year([Issue Date])+3,Month([Issue Date]),Day([Issue Date]))

Valid: Nothing - this is also calculated in the form (=IIf([Expiry Date]>=Date(),"Yes","Over Due"))

Is there a way to calculate the same information before it gets to the form? This needs to be updating the table everytime so to ensure there is inforamtion available when creating a report?

I then want to produce a report which shows only the "Overdue" records but because this info is calculated in the form and currently not in the base table, when putting "Overdue" in the criteria section of a query under the "Valid" field, obviously there is nothing in there and it displays nothing.

Please can you help? I'm sure there is a simple solution...

Many thanks in advance.

 

[HarleyQuinn]

You could base the query on the values in the form but I'd skip that and just port the calculations straight into the query you want to create, they'll work fine.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Powerhouse123]

Thank you, this has been most helpful.

I have put the calculations into the query and they work fine but the criteria function is not.

Eg: In the query

Field - Expriy Date: DateSerial(Year([Issue Date])+3,Month([Issue Date]),Day([Issue Date]))

Field - Valid: IIf([Expiry Date]>=Date(),"Yes","Over Due")
Criteria: "Over Due"

This just brings back all records not just the over due ones...

Any ideas?

Thank you again.

 

[HarleyQuinn]

Just use a reverse of the calculation criteria in the fields criteria :

[Expiry Date]<Date()

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Powerhouse123]

Unfortunately this does not work...unless I am doing it wrong...

In my query and under the field, "Valid" and in the criteria section I typed [Expiry Date]<Date()...nothing was returned when looking at the results of the query.

I then thought about creating another query based on this calculating query and then in the criteria section of this new query I put "Over Due" to just get those records back but this said "Data Mismatch..." and didnt work either!

Sorry to be a pain.

Thanks again in advance.

 

[HarleyQuinn]

Sorry, I didn't explain myself very well at all there . Include the Expiry Date field (but don't output it) and put the criteria in there (just the <Date() bit though).

Strangely though, for me, both of the following criteria worked correctly (only displaying "Over Due" records) on the Valid field I was creating:

"Over Due"
and
<>"Yes"

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Powerhouse123]

Still couldn't get to grips with the <Date() but good news for me...adding the criteria of "Over Due" seems to work now! I guess Access was having a funny moment! Shutdown and restart did the trick.

Many thanks for all your help.

This is a fantastic service.

 

[HarleyQuinn]

Glad I could help you get it sorted, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Powerhouse123]

Hi Again,

It appears I was too hastey!

What I thought was working is not doing what I want it to do!!

As previously mentioned I want only the criteria of "Over Due" to appear in a query.

Eg: In the query

Field - Expriy Date: DateSerial(Year([Issue Date])+3,Month([Issue Date]),Day([Issue Date]))

Field - Valid: IIf([Expiry Date]>=Date(),"Yes","Over Due")
Criteria: "Over Due"

This just puts "Over Due" for all the records rather than returning those that say "Over Due".

Sorry for my misunderstanding...you still deserve a star!!

I have tried all the variations of what you previously mentioned about <Date() etc but it has all failed!!

 

[HarleyQuinn]

Strange, I'm having trouble finding a variation that doesn't work on mine...

Could you post an example of the data in your underlying table so I can have a look at it along with the structure.

Could you also post the SQL of the query you've got at the moment please?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Powerhouse123]

Base table structure and data...(ovbiously in the normal format but I tried to format it as best I can for this...) The data section is repeated for different people but there is no Expiry date in the base table it is all calculated in the query...

FIELD DATA
ID 1 2
Name Joe Smith Mary Jones
Issue Date 01/01/2005 14/12/2004
Expiry Nothing-calculated in qry Nothing
Date Nothing-calculated in qry Nothing
Valid Nothing-calculated in qry Nothing

SQL View of the Query...

SELECT [SQI CRB].ID, [SQI CRB].[Forename(s)], [SQI CRB].Surname, [SQI CRB].[Job Title], [SQI CRB].Agency, [SQI CRB].[Disclosure Number], [SQI CRB].[Issue Date], IIf([Expiry Date]>=Date(),"Yes","3 Years Old - Over Due for ContactPoint") AS Valid, DateSerial(Year([Issue Date])+2,Month([Issue Date])+9,Day([Issue Date])) AS [3 Month Alert Date], IIf([3 Month Alert Date]>=Date(),"","Prepare ECRB Renewal") AS [3 Month Alert], [SQI CRB].Ext, [SQI CRB].Email
FROM [SQI CRB]
WHERE (((IIf([Expiry Date]>=Date(),"Yes","Over Due"))="Over Due"));

Many thanks, I hope this is what you wanted...

 

[HarleyQuinn]

That's great, thanks.

Right, let's have a go. Just for ease I've done it as a subquery wrapped with the criteria query.

select * from
(
SELECT [SQI CRB].[ID], [SQI CRB].[Name], [SQI CRB].[IssueDate], DateSerial(Year([IssueDate])+3,Month([IssueDate]),Day([IssueDate])) as [Expiry Date],  IIf([Expiry Date]>=Date(),"Yes","3 Years Old - Over Due for ContactPoint") AS Valid, DateSerial(Year([IssueDate])+2,Month([IssueDate])+9,Day([IssueDate])) AS [3 Month Alert Date], IIf([3 Month Alert Date]>=Date(),"","Prepare ECRB Renewal") AS [3 Month Alert]
FROM [SQI CRB]
)
WHERE Valid="3 Years Old - Over Due for ContactPoint";

I had to take some fields out as I worked with the test data you gave me but you should get the idea. I changed the Issue Date for Mary Jones to 14/12/2003 so she was classed as Over Due but the query seemed to return all of the things you wanted.

Give it a try and let me know if it's any good for you.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Powerhouse123]

Hello,

After several hours of testing I have finally got to the bottom of why I was having problems. The test data I gave you had Issue Dates in the fields but the table I was working off, some fields are empty.

This causes the query to break and the error message of Data Mismatch etc. Is there any way the code can incorporate cominf across a blank field?

Kind regards

 

[HarleyQuinn]

Have a look at the nz() function.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

I'm glad you've found the problem. I'd have a look at the nz() function.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

oops!, the problems of trying to answer queries on your mobile phone while on a train

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.