Date query in VBA Module

[NoCanDo]

Hi

I'm new to Access Monster, but not access. Using Access 2003 and would like
to use the 1st April as the start date for any particular reporting year so
that I can pull out historic data from a much larger dataset.

My very simple query selects a single field record (Date format) from a table
and I want to use that as the basis of another query on another table where
it is used as the Between 'date' And Date().

To simplify matters I created a very two simple tables called:
Temp - Year which contins one field YearStartDate (Date/Time data type)
DateTest which contains two fields: EraDate (Date/Time data type) and
TestNumber (Number data type)

I want to use the [Temp - Year].[YearStartDate] as the basis of another
simple query DateTest1 which queries table DateTest (without linking the
tables).

I have tried using a module (called FinancialYear see below) and used that as
the expression but it will not pull out any records. The vba for the module
is sooo basic!

Public Function FinancialYearEnd()
Set rst = CurrentDb.OpenRecordset("YearDateAct")
End Function

Query DateTest1
SELECT DateTest.EraDate, DateTest.TestNumber
FROM DateTest
WHERE (((DateTest.EraDate)=FinancialYearEnd()));

I know something is wrong but I'm unable to see where!

Hope someone can help.

Regards

NoCanDo

 

[Duane Hookom]

I don't see anywhere in your posting that you tell us what "YearDateAct" is.
You haven't Dim'd rst and your function won't return any value.

If you want to use FinancialYearEnd() to return the one field value from the
one record in [Temp - Year] then your could use

Public Function FinancialYearEnd() as Date
FinancialYearEnd = DLookup("YearStartDate","[Temp - Year]")
End Function

 

[NoCanDo via AccessMonster.com]

Hi Duane

Many thanks for your reply, and many apologies for not getting back to you
sooner – escaped early on Fri.

Have checked out your reply and apologies again I missed out the detail:

YearDateAct is a query on Table: Temp – Year which returns the only record
from the only field YearStartDate

Have checked out your Module


Public Function FinancialYearEnd() As Date
FinancialYearEnd = DLookup("YearStartDate", "[Temp - Year]")
End Function

When used as part of a query in the QBE grid as = FinancialYearEnd it returns
Undefined function ‘FinancialYearEnd’ in expression.

The SQL for the query is as below

SELECT DateTest.EraDate, DateTest.TestNumber
FROM DateTest
WHERE (((DateTest.EraDate)=FinancialYearEnd()));


Have also checked out:

Public Function FinYear1()
Dim rst As Date
Set rst = CurrentDb.OpenRecordset("YearDateAct")
End Function

And this also returns “Undefined function …

Hope you can advise.

NoCanDo

 

[John Spencer]

If your table has one record you can just include the table in your query.
Simply use the VBA DLookup Function
SELECT TableOne.*
FROM TableOne
WHERE TableOne.DateField >= DLookup("DateField","OneRecordTable")
and TableOne.DateField <
DateAdd("yyyy",1,DLookup("DateField","OneRecordTable"))


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Duane Hookom]

Your function should be saved in a standard module and the name of the module
can't be the name of the function.

 

[NoCanDo via AccessMonster.com]

Hi John

Many many thanks for the reply - your concept is sound - and uses:

SELECT DateTest.EraDate, DateTest.TestNumber
FROM DateTest, [Temp - Year]
WHERE (((DateTest.EraDate) Between [Temp - Year].[YearStartDate] And Date()))
;

That works fine - without issue however, my problem now gets worse! When
applying this concept to what I need to use it in returns a Front End error:

You tried to execute a query that does not include the specified expression '
[dbPReferrals AQT].[Current Source Referral (OP)]="gp" Or [dbPReferrals
AQT].[Current Source Referral (OP)]="dentist" Or [dbPReferrals AQT].
[Current Source Referral (OP)]="patientgp" Or [dbPReferrals AQT].[Current
Source Referral (OP)]="other' as part of an aggregate function.

This occurs when I run the much more complex query (SQL below):

SELECT [Temp - MPI CIW].[Casenote Number], [dbPReferrals AQT].[NHS Number],
[dbPReferrals AQT].[Patient Surname], [dbPReferrals AQT].[Patient First
Name], [dbPReferrals AQT].[Date of Birth], [dbPReferrals AQT].[Admin
Category], [dbPReferrals AQT].[Consultant (Primary)], [dbPReferrals AQT]
.[Specialty (Primary)], Left([Specialty code (Primary)],3) AS [Specialty Code]
, [dbPReferrals AQT].[Current Source Referral (OP)], [dbPReferrals AQT].
[Referral date (GP)], [dbPReferrals AQT].[Referral Received Date],
[episode added date]-[referral received date] AS [Days from received to
Medway], [dbPReferrals AQT].[Referral Term], [dbPReferrals AQT].[First
Appointment Date], [dbPReferrals AQT].[First Appointment Outcome],
[dbPReferrals AQT].[Appointment Type (NF)], [dbPReferrals AQT].[Last
Patient Cancelled Appointment], [dbPReferrals AQT].[Last Patient DNA Date],
[dbPReferrals AQT].[Cancel Reason], [dbPReferrals AQT].[Outcome
(Episode)], [dbPReferrals AQT].[GP (Episode)], [dbPReferrals AQT].[GP
Practice Code], [dbPReferrals AQT].[PCT of GP Practice], [dbPReferrals
AQT].EpisodeRef, [dbPReferrals AQT].[District Number]
FROM [Temp - Year], [dbPReferrals AQT] INNER JOIN [Temp - MPI CIW] ON
[dbPReferrals AQT].[District Number] = [Temp - MPI CIW].[District Number]
GROUP BY [Temp - MPI CIW].[Casenote Number], [dbPReferrals AQT].[NHS
Number], [dbPReferrals AQT].[Patient Surname], [dbPReferrals AQT].
[Patient First Name], [dbPReferrals AQT].[Date of Birth], [dbPReferrals
AQT].[Admin Category], [dbPReferrals AQT].[Consultant (Primary)],
[dbPReferrals AQT].[Specialty (Primary)], Left([Specialty code (Primary)],
3), [dbPReferrals AQT].[Current Source Referral (OP)], [dbPReferrals
AQT].[Referral date (GP)], [dbPReferrals AQT].[Referral Received Date],
[episode added date]-[referral received date], [dbPReferrals AQT].
[Referral Term], [dbPReferrals AQT].[First Appointment Date],
[dbPReferrals AQT].[First Appointment Outcome], [dbPReferrals AQT].
[Appointment Type (NF)], [dbPReferrals AQT].[Last Patient Cancelled
Appointment], [dbPReferrals AQT].[Last Patient DNA Date], [dbPReferrals
AQT].[Cancel Reason], [dbPReferrals AQT].[Outcome (Episode)],
[dbPReferrals AQT].[GP (Episode)], [dbPReferrals AQT].[GP Practice Code]
, [dbPReferrals AQT].[PCT of GP Practice], [dbPReferrals AQT].
EpisodeRef, [dbPReferrals AQT].[District Number]
HAVING ((([dbPReferrals AQT].[Current Source Referral (OP)])="gp" Or (
[dbPReferrals AQT].[Current Source Referral (OP)])="dentist" Or (
[dbPReferrals AQT].[Current Source Referral (OP)])="patientgp" Or (
[dbPReferrals AQT].[Current Source Referral (OP)])="other gp" Or (
[dbPReferrals AQT].[Current Source Referral (OP)]) Like "other prac*" Or (
[dbPReferrals AQT].[Current Source Referral (OP)]) Like "*GPSpecInt*") AND
(([dbPReferrals AQT].[Referral date (GP)]) Between [Temp - Year].
[YearStartDate] And DateAdd("d",1-DatePart("w",Date(),1),Date())));

This is a query which pulls data from two linked tables (dbPReferrals AQT)
and (Temp - MPI CIW) and the unlinked table (Temp - Year) with the criteria:

Current Source Referral (OP) = "gp" Or "dentist" Or "patientgp" Or "other gp"
Or Like "other prac*" Or Like "*GPSpecInt*" AND
Referral date (GP)
Between [Temp - Year].[YearStartDate] And DateAdd("d",1-DatePart("w",Date(),1)
,Date())


Phew!

Apologies for the length of the SQL etc - you can appreciate why I wanted to
use test data in smaller more easy to use tables.

If you can shed a light I would be more than grateful.

Here's hoping ...

Kind regards and many thanks to yourself and Duane for your help.

NoCanDo

 

[John Spencer]

First, since you are not using any aggregate functions there is no need
to GROUP BY. You can use the DISTINCT operator in the query to get the
same results and don't need the GROUP BY clause at all.

Second, even with that you should be using a WHERE clause to filter your
records and not a HAVING clause. A Where clause is much more
efficient. You only use the HAVING clause when you need to filter on an
aggregate result (SUM, Avg, Max, Min, etc)

So I would rewrite the query as

SELECT DISTINCT [Temp - MPI CIW].[Casenote Number]
, [dbPReferrals AQT].[NHS Number]
, [dbPReferrals AQT].[Patient Surname]
, [dbPReferrals AQT].[Patient First Name]
, [dbPReferrals AQT].[Date of Birth]
, [dbPReferrals AQT].[Admin Category]
, [dbPReferrals AQT].[Consultant (Primary)]
, [dbPReferrals AQT].[Specialty (Primary)]
, Left([Specialty code (Primary)],3) AS [Specialty Code]
, [dbPReferrals AQT].[Current Source Referral (OP)]
, [dbPReferrals AQT].[Referral date (GP)]
, [dbPReferrals AQT].[Referral Received Date]
, [episode added date]-[referral received date]
AS [Days from received to Medway]
, [dbPReferrals AQT].[Referral Term]
, [dbPReferrals AQT].[First Appointment Date]
, [dbPReferrals AQT].[First Appointment Outcome]
, [dbPReferrals AQT].[Appointment Type (NF)]
, [dbPReferrals AQT].[Last Patient Cancelled Appointment]
, [dbPReferrals AQT].[Last Patient DNA Date]
, [dbPReferrals AQT].[Cancel Reason]
, [dbPReferrals AQT].[Outcome (Episode)]
, [dbPReferrals AQT].[GP (Episode)]
, [dbPReferrals AQT].[GP Practice Code]
, [dbPReferrals AQT].[PCT of GP Practice]
, [dbPReferrals AQT].EpisodeRef
, [dbPReferrals AQT].[District Number]

FROM [Temp - Year],
[dbPReferrals AQT] INNER JOIN [Temp - MPI CIW]
ON [dbPReferrals AQT].[District Number] =
[Temp - MPI CIW].[District Number]


WHERE (
[dbPReferrals AQT].[Current Source Referral (OP)]="gp"
Or [dbPReferrals AQT].[Current Source Referral (OP)]="dentist"
Or [dbPReferrals AQT].[Current Source Referral (OP)]="patientgp"
Or [dbPReferrals AQT].[Current Source Referral (OP)]="other gp"
Or [dbPReferrals AQT].[Current Source Referral (OP)]
Like "other prac*"
Or [dbPReferrals AQT].[Current Source Referral (OP)]
Like "*GPSpecInt*"
)
AND
[dbPReferrals AQT].[Referral date (GP)]
Between [Temp - Year].[YearStartDate]
And DateAdd("d",1-DatePart("w",Date(),1),Date());

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi John

Many many thanks for the reply - your concept is sound - and uses:

SELECT DateTest.EraDate, DateTest.TestNumber
FROM DateTest, [Temp - Year]
WHERE (((DateTest.EraDate) Between [Temp - Year].[YearStartDate] And Date()))
;

That works fine - without issue however, my problem now gets worse! When
applying this concept to what I need to use it in returns a Front End error:

You tried to execute a query that does not include the specified expression '
[dbPReferrals AQT].[Current Source Referral (OP)]="gp" Or [dbPReferrals
AQT].[Current Source Referral (OP)]="dentist" Or [dbPReferrals AQT].
[Current Source Referral (OP)]="patientgp" Or [dbPReferrals AQT].[Current
Source Referral (OP)]="other' as part of an aggregate function.

This occurs when I run the much more complex query (SQL below):

 

[NoCanDo via AccessMonster.com]

Hi John

What can I say except WOW! Absolutely beyond any expectations I ever had
when using this forum.

This will take some understanding as it might be beyond my current knowledge
base - but I'll be checking it out for sure.

Many many gratfeul thanks for your assitance and please accept my apologies
for my late reply - as is usual with a lot of jobs, you tend to get
sidetracked into other tasks and I've only just managed to get back where I
was before ...

John, thank you.

Kind regards

NoCanDo (Paul)