WHY DOES THIS CODE WORK AT ALL?

[Guest]

Hi All,
Please help me out.
Filtering a form with dates in Access will drive me nuts soon. I use this
filter on my form:

If Not IsNull(Ending) And Not IsNull(Starting) Then
Me.Filter = Me.Filter & " And [TDate]>= " & "'" & Format(Starting,
"dd-mmm-yyyy") & "'" & " And [TDate]<=" & "'" & Format(Ending, "dd-mmm-yyyy")
& "'"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to dates.", vbInformation,
"ECKANKAR AREA ADMIN"
End If

The filter works ERRATICALLY, returning recordsets based on a hidden formula
which I am yet to decode. The problem is that it is the only code that has
returned any recordset at all.

When I use the # sign instead of literal ' sign, it returns no records at
all. If I remove the format(date, "dd-mmm-yyyy"), I get errors or no records.
In fact when I format it any other way (like mm/dd/yyyy), it does not work at
all.

The same code works perfectly without any # or ' sign in another project
like this:

If Not IsNull(Ending) And Not IsNull(Starting) Then
Me.Filter = Me.Filter & " And [TDate]>= Form!Donations!Starting And
[TDate]<=Forms!Donations!Ending"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to dates.", vbInformation,
"ECKANKAR AREA ADMIN"
End If

The only difference I can see is that the form that does not work is based
on a complex union query (with dates formatted to dd-mmm-yyyy as in the rest
of the project) and this query does work well, while the form that works was
based on tables or a simple query. Could this have accounted for the errors?

WHAT CAN I DO TO GET MY FORM TO FILTER THE UNION QUERY ACCORDING TO DATES?

 

[Douglas J. Steele]

Is TDate a Date field, or is it a Text field?

 

[Guest]

It is a date field. It was created as such in the query: SELECT bla
bla...Format(PatientGroup.DDate,"dd-mmm-yyyy") AS TDate,...etc. And when the
query runs, TDate displays as formated.
--
Glint

Is TDate a Date field, or is it a Text field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi All,
Please help me out.
Filtering a form with dates in Access will drive me nuts soon. I use this
filter on my form:

If Not IsNull(Ending) And Not IsNull(Starting) Then
Me.Filter = Me.Filter & " And [TDate]>= " & "'" & Format(Starting,
"dd-mmm-yyyy") & "'" & " And [TDate]<=" & "'" & Format(Ending,
"dd-mmm-yyyy")
& "'"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to dates.", vbInformation,
"ECKANKAR AREA ADMIN"
End If

The filter works ERRATICALLY, returning recordsets based on a hidden
formula
which I am yet to decode. The problem is that it is the only code that has
returned any recordset at all.

When I use the # sign instead of literal ' sign, it returns no records at
all. If I remove the format(date, "dd-mmm-yyyy"), I get errors or no
records.
In fact when I format it any other way (like mm/dd/yyyy), it does not work
at
all.

The same code works perfectly without any # or ' sign in another project
like this:

If Not IsNull(Ending) And Not IsNull(Starting) Then
Me.Filter = Me.Filter & " And [TDate]>= Form!Donations!Starting And
[TDate]<=Forms!Donations!Ending"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to dates.", vbInformation,
"ECKANKAR AREA ADMIN"
End If

The only difference I can see is that the form that does not work is based
on a complex union query (with dates formatted to dd-mmm-yyyy as in the
rest
of the project) and this query does work well, while the form that works
was
based on tables or a simple query. Could this have accounted for the
errors?

WHAT CAN I DO TO GET MY FORM TO FILTER THE UNION QUERY ACCORDING TO DATES?

 

[pietlinden]

It is a date field. It was created as such in the query: SELECT bla
bla...Format(PatientGroup.DDate,"dd-mmm-yyyy") AS TDate,...etc. And when the
query runs, TDate displays as formated.
--
Glint

Is TDate a Date field, or is it a Text field?

Hi All,
Please help me out.
Filtering a form with dates in Access will drive me nuts soon. I use this
filter on my form:
If Not IsNull(Ending) And Not IsNull(Starting) Then
Me.Filter = Me.Filter & " And [TDate]>= " & "'" & Format(Starting,
"dd-mmm-yyyy") & "'" & " And [TDate]<=" & "'" & Format(Ending,
"dd-mmm-yyyy")
& "'"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to dates.", vbInformation,
"ECKANKAR AREA ADMIN"
End If
The filter works ERRATICALLY, returning recordsets based on a hidden
formula
which I am yet to decode. The problem is that it is the only code that has
returned any recordset at all.
When I use the # sign instead of literal ' sign, it returns no records at
all. If I remove the format(date, "dd-mmm-yyyy"), I get errors or no
records.
In fact when I format it any other way (like mm/dd/yyyy), it does not work
at
all.
The same code works perfectly without any # or ' sign in another project
like this:
If Not IsNull(Ending) And Not IsNull(Starting) Then
Me.Filter = Me.Filter & " And [TDate]>= Form!Donations!Starting And
[TDate]<=Forms!Donations!Ending"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to dates.", vbInformation,
"ECKANKAR AREA ADMIN"
End If
The only difference I can see is that the form that does not work is based
on a complex union query (with dates formatted to dd-mmm-yyyy as in the
rest
of the project) and this query does work well, while the form that works
was
based on tables or a simple query. Could this have accounted for the
errors?
WHAT CAN I DO TO GET MY FORM TO FILTER THE UNION QUERY ACCORDING TO DATES?

the format function returns a String, not a Date. You need CDate()
for that.

 

[John W. Vinson]

It is a date field. It was created as such in the query: SELECT bla
bla...Format(PatientGroup.DDate,"dd-mmm-yyyy") AS TDate,...etc. And when the
query runs, TDate displays as formated.

Then it IS a Text field, not a date field! The Format() function returns a
String value, not a Date/Time value.

John W. Vinson [MVP]

 

[Guest]

I have no doubt in my mind that the original fields on which the union query
date field was based ARE date fields. The reason for the formating into
dd-mmm-yyyy is to make the output of the query uniform with the rest of the
project, after I noticed that the union query disregarded the original format
of those date fields. I have not noticed any problems in this regard.

Interestingly, when I removed the formating from the union query, things did
not improve at all.

 

[Guest]

Let me state the problem a kittle more clearly.

First, I have four OR five tables that have DDate fields properly set, and
formatted as dd-mmm-yyyy like the rest of the project. Tables and their forms
have been working fine. So far, so good. Next, I construct a union query to
gather all their data in one point. Union Query works fine but returns all
dates as mm/dd/yyyy. So I format all dates individually to dd-mmm-yyyy, and
Union Query is happy. So far, so good too.

Then I base a form on my union query. Later I found that the form would only
return dates as the query output; I could not force a textbox to change
mm/dd/yyyy to dd-mmm-yyyy, and I thought this was strange. So the need to
format the dates in the union query was even more pronounced. Form then works
fine, filtering with other parameters, except date. I have tried all I could
and have not got a winning formula yet to filter the records of the union
query with dates.

WHAT AM I DOING WRONG?

 

[Rick Brandt]

Let me state the problem a kittle more clearly.

First, I have four OR five tables that have DDate fields properly
set, and formatted as dd-mmm-yyyy like the rest of the project.
Tables and their forms have been working fine. So far, so good.
Next, I construct a union query to gather all their data in one
point. Union Query works fine but returns all dates as mm/dd/yyyy. So
I format all dates individually to dd-mmm-yyyy, and Union Query is
happy. So far, so good too.

Then I base a form on my union query. Later I found that the form
would only return dates as the query output; I could not force a
textbox to change mm/dd/yyyy to dd-mmm-yyyy, and I thought this was
strange. So the need to format the dates in the union query was even
more pronounced. Form then works fine, filtering with other
parameters, except date. I have tried all I could and have not got a
winning formula yet to filter the records of the union query with
dates.

WHAT AM I DOING WRONG?

You are worrying about formatting in the wrong places. Don't use formatting
anywhere except where the user will see it. Not in tables (ever), not in
queries (except for grouping), not in code (except to build SQL strings). Then
you are always working with actual dates (not strings) and you are comparing
apples to apples.

Also realize the difference between the format *property* and the Format()
*function*. The former does not change the underlying data type. All it does
is affect its appearance on the screen within Access. The latter always returns
a string. It will sort as a string, and you must do comparisons as a string.

I can use the format property to display just the year from a DateTime field and
still apply criteria for a particular month. That is because the full DateTime
value is still there to be evaluated. If I use the Format() function to return
the year then all I have in my output is the characters that make up the year
and that is all I have available to apply criteria to.

 

[Douglas J. Steele]

Why do you feel there's a need to format the dates in the union query? As
has been pointed out, using the Format function converts the dates into
strings, which changes the sort order. That's likely why you're getting odd
results: with strings, you'll find that 02-Dec-2007 will be between
01-Jan-2007 and 31-Jan-2007 (in fact, the only dates in 2007 which WON'T be
between 01-Jan-2007 and 31-Jan-2007 will be 01-Apr-2007, 01-Aug-2007,
01-Dec-2007, 01-Feb-2007, 31-Jul-2007, 31-Mar-2007, 31-May-2007 and
31-Oct-2007)

You should always be working with forms, so the only place you should need
to worry about the format is in the text boxes used to display the values.

Leave the date fields unformatted in the union query, and try the following
filter:

Me.Filter = Me.Filter & " And ([TDate]>= " & _
Format(Starting, "\#dd\-mmm\-yyyy\#") & _
" And [TDate]<=" & Format(Ending, "\#dd\-mmm\-yyyy\#") & ")"

or

Me.Filter = Me.Filter & " And ([TDate] BETWEEN " & _
Format(Starting, "\#dd\-mmm\-yyyy\#") & _
" And " & Format(Ending, "\#dd\-mmm\-yyyy\#") & ")"

 

[Guest]

Thanks, Guys.
I removed the formatting on the query, and used the code as suggested by
Gouglas, and everything was magic thereafter. However, the textbox displaying
TDate refused to accept any formatting, and continues to display as
mm/dd/yyyy. But that is a small price to pay.
Again, thanks to you all.

 

[John W. Vinson]

I removed the formatting on the query, and used the code as suggested by
Gouglas, and everything was magic thereafter. However, the textbox displaying
TDate refused to accept any formatting, and continues to display as
mm/dd/yyyy. But that is a small price to pay.

That's the odd part to me. In what way does it "refuse"? Is this a Form
textbox, a Report textbox, or what?

One (hopefully unneeded!!) getaround would be to set the Control Source of the
textbox to

=Format([datefield], "yyyy-mmm-dd")

or whatever format you want.

John W. Vinson [MVP]

 

[Guest]

Hi Guys,

Apparently, I rejoiced too early. The working coed I posted earlier did not
return correct recrd sets all the time. It appears I did not take the hint
from each of you literarily.

To answer John, yes, I am working with a form that has textboxes. Starting
and Ending are unbound textboxes with format properties set to "dd-mmm-yyyy".
My first mistake was that this was enough; I did not realize I am still
working with strings displayed in a date format. So my textboxes were not yet
date fields.

Secondly, I had assumed that since my tables had date fields, the ensuing
union query would inherit the properties of those fields. Apparently, it did
not. TDate was the output (in the union query) of the date fields of my
tables, and somehow TDate on the form REFUSED to accept any format property
except the one stated in the union query. Until I used CDate function as
suggested by Pietlinden. So again, I have to admit that TDate could not have
been a date field in the union query until it was converted.

Finally, I used Douglas's suggestion in the filter, only as yyyy-mm-dd thus:

Me.Filter = Me.Filter & " And ([TDate] BETWEEN " & _
Format(Starting, "\#yyyy\-mm\-dd\#") & _
" And " & Format(Ending, "\#yyyy\-mm\-dd\#") & ")"

This time, there appears to be some sanity, for now.

My questions are:
1. Why does the union query need CDate function for the output of date
fields if those fields were bona fide date fields in their tables?
2. I thought Access could read a date in any format, so why do I need to
specify yyyy-mm-dd in formating my unbound textboxes in order to filter
records accurately?

My fear is that I have not understood this problem yet.
--
Glint

I removed the formatting on the query, and used the code as suggested by
Gouglas, and everything was magic thereafter. However, the textbox displaying
TDate refused to accept any formatting, and continues to display as
mm/dd/yyyy. But that is a small price to pay.

That's the odd part to me. In what way does it "refuse"? Is this a Form
textbox, a Report textbox, or what?

One (hopefully unneeded!!) getaround would be to set the Control Source of the
textbox to

=Format([datefield], "yyyy-mmm-dd")

or whatever format you want.

John W. Vinson [MVP]

 

[Douglas J. Steele]

My questions are:
1. Why does the union query need CDate function for the output of date
fields if those fields were bona fide date fields in their tables?

You've never actually shown us the Union query, nor told us anything about
the underlying tables so it's pretty difficult to answer.

2. I thought Access could read a date in any format, so why do I need to
specify yyyy-mm-dd in formating my unbound textboxes in order to filter
records accurately?

Access will always treat nn/nn/nnnn as mm/dd/yyyy, regardless of what the
Regional Settings are. You don't have to use yyyy-mm-dd, but you must use an
unambiguous format. dd-mmm-yyyy should work fine: a problem you were
experiencing was that you weren't delimiting the values with octothorpes
(#).

You may want to read Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I wrote in my September 2003
Access Answers column for Pinnacle Publication's "Smart Access" newsletter.
(The column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Guest]

Thanks again, Douglas.
I think I am getting some answers to my posers after reading Allen Browne's
article. I am posting the union query nonetheless because I am sure I have a
lot to gain from your advice. I only fear for my other codes that have
appeared to "work" in some other projects; perhaps some accidents are waiting
to happen.

Please find herewith the ammended union query that eventually "worked". The
part that has been of concern is the TDate field that was derived from DDate:

SELECT PatientGroup.PatientGroupID AS TblID, "PATIENTGROUP" AS TblName,
PatientGroup.PatientGroup AS PatientName, CDate(PatientGroup.DDate) AS TDate,
"GROUP REGISTRATION" AS [Transaction], PatientGroup.Bill, 0 AS Payment, Null
AS AttendanceID, Null AS ConsultationID, Null AS PaymentID,
PatientGroup.PatientGroupID AS [Group]
FROM PatientGroup
WHERE (((PatientGroup.Bill) Is Not Null))
UNION ALL SELECT Patients.PatientID AS TblID, "PATIENTS" AS TblName,
Patients.[Surname] & ", " & [OtherNames] AS PatientName,
CDate(Patients.DDate) AS TDate, "PATIENT REGISTRATION" AS [Transaction],
Patients.Bill, 0 AS Payment, Null AS AttendanceID, Null AS ConsultationID,
Null AS PaymentID, Patients.PatientGroup
FROM Patients
WHERE (((Patients.Bill) Is Not Null))
UNION ALL SELECT PatientObservations.ObservationID AS TblID, "OBSERVATIONS"
AS TblName, DLookUp("[PatientName]","qGroupFromAttendance","[AttendanceID]="
& [attendanceid]) AS PatientName, CDate(PatientObservations.DDate) AS TDate,
"OBSERVATION" AS [Transaction], PatientObservations.Bill, 0 AS Payment,
PatientObservations.AttendanceID, Null AS ConsultationID, Null AS PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM PatientObservations
WHERE (((PatientObservations.Bill) Is Not Null))
UNION ALL SELECT Consultation.ConsultationID AS TblID, "CONSULTATION" AS
TblName, DLookUp("[PatientName]","qGroupFromAttendance","[AttendanceID]=" &
[attendanceid]) AS PatientName,
CDate(DLookUp("[DDate]","qGroupFromAttendance","[AttendanceID]=" &
[attendanceid])) AS TDate, "CONSULTATION" AS [Transaction],
Consultation.Bill, 0 AS Payment, Consultation.AttendanceID,
Consultation.ConsultationID, Null AS PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Consultation
WHERE (((Consultation.Bill) Is Not Null))
UNION ALL SELECT Requests.RequestsID AS TblID, "REQUESTS" AS TblName,
DLookUp("[PatientName]","qConsultation","[ConsultationID]=" &
[ConsultationID]) AS PatientName,
CDate(DLookUp("[DDate]","qConsultation","[ConsultationID]=" &
[ConsultationID])) AS TDate, "INVESTIGATION" AS [Transaction], Requests.Bill,
0 AS Payment, DLookUp("[AttendanceID]","Consultation","[ConsultationID]=" &
[ConsultationID]) AS AttendanceID, Requests.ConsultationID, Null AS
PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Requests
WHERE (((Requests.Bill) Is Not Null))
UNION ALL SELECT Rx.RxID AS TblID, "Rx" AS TblName,
DLookUp("[PatientName]","qConsultation","[ConsultationID]=" &
[ConsultationID]) AS PatientName,
CDate(DLookUp("[DDate]","qConsultation","[ConsultationID]=" &
[ConsultationID])) AS TDate, "DRUG THERAPY" AS [Transaction], Rx.Bill, 0 AS
Payment, DLookUp("[AttendanceID]","Consultation","[ConsultationID]=" &
[ConsultationID]) AS AttendanceID, Rx.ConsultationID, Null AS PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Rx
WHERE (((Rx.Bill) Is Not Null))
UNION ALL SELECT Payments.PaymentID AS TblID, "PAYMENTS" AS TblName,
DLookUp("[PatientGroup]","PatientGroup","[PatientGroupID]=" & [PatientGroup])
AS PatientName, CDate(Payments.DDate) AS TDate, "PAYMENT" AS [Transaction], 0
AS Bill, Payments.Amount AS Payment, Null AS AttendanceID, Null AS
ConsultationID, Payments.PaymentID, Payments.PatientGroup
FROM Payments
WHERE (((Payments.Amount) Is Not Null));

The PatientGroup is a table that has PatientGroupID set to Autonumber and is
the primary field, PatientGroup is a text field, DDate is a date field with
format dd-mmm-yyyy. Bill and Payment are currency fields. Other tables are
similar, albeit with different names. I included the table names because they
happen to have the same names as their forms, and I want to be able to call
up any record for editing with the primary field and the form name.

I appreciate any comments and suggestions you may have on the query in
general and the TDate field in particular.

I should mention that I have not been able to open the databases in the
Smart Access newsletter; a message comes up that I am trying to open a
read-only database etc.

 

[Douglas J. Steele]

I haven't looked at it in great detail, but if DDate is in fact a Date
field, I can't think of any legitimate reason why you'd need to use CDate on
it.

The use of DLookups in queries will make the query far slower. Looking at
some of the subselects, I'd think that you'd be able to join tables together
rather than using DLookup. For example, rather than

SELECT PatientObservations.ObservationID AS TblID, "OBSERVATIONS"
AS TblName,
DLookUp("[PatientName]","qGroupFromAttendance","[AttendanceID]="
& [attendanceid]) AS PatientName, CDate(PatientObservations.DDate) AS
TDate,
"OBSERVATION" AS [Transaction], PatientObservations.Bill, 0 AS Payment,
PatientObservations.AttendanceID, Null AS ConsultationID, Null AS
PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM PatientObservations

I would think you could use

SELECT PatientObservations.ObservationID AS TblID, "OBSERVATIONS"
AS TblName, qGroupFromAttendance.[PatientName],
CDate(PatientObservations.DDate) AS TDate,
"OBSERVATION" AS [Transaction], PatientObservations.Bill, 0 AS Payment,
PatientObservations.AttendanceID, Null AS ConsultationID, Null AS PaymentID,
qGroupFromAttendance.[PatientGroupID] AS Group
FROM PatientObservations INNER JOIN qGroupFromAttendance
ON PatientObservations.attendanceid = qGroupFromAttendance.AttendanceID

I have no idea why you're running into a read-only message with the
database. I just downloaded it myself, and there's definitely no read-only
message. Did you perhaps misread the message? The sample database is in
Access 97 format, so you would get a message about converting it if you
opened it in a later version.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks again, Douglas.
I think I am getting some answers to my posers after reading Allen
Browne's
article. I am posting the union query nonetheless because I am sure I have
a
lot to gain from your advice. I only fear for my other codes that have
appeared to "work" in some other projects; perhaps some accidents are
waiting
to happen.

Please find herewith the ammended union query that eventually "worked".
The
part that has been of concern is the TDate field that was derived from
DDate:

SELECT PatientGroup.PatientGroupID AS TblID, "PATIENTGROUP" AS TblName,
PatientGroup.PatientGroup AS PatientName, CDate(PatientGroup.DDate) AS
TDate,
"GROUP REGISTRATION" AS [Transaction], PatientGroup.Bill, 0 AS Payment,
Null
AS AttendanceID, Null AS ConsultationID, Null AS PaymentID,
PatientGroup.PatientGroupID AS [Group]
FROM PatientGroup
WHERE (((PatientGroup.Bill) Is Not Null))
UNION ALL SELECT Patients.PatientID AS TblID, "PATIENTS" AS TblName,
Patients.[Surname] & ", " & [OtherNames] AS PatientName,
CDate(Patients.DDate) AS TDate, "PATIENT REGISTRATION" AS [Transaction],
Patients.Bill, 0 AS Payment, Null AS AttendanceID, Null AS ConsultationID,
Null AS PaymentID, Patients.PatientGroup
FROM Patients
WHERE (((Patients.Bill) Is Not Null))
UNION ALL SELECT PatientObservations.ObservationID AS TblID,
"OBSERVATIONS"
AS TblName,
DLookUp("[PatientName]","qGroupFromAttendance","[AttendanceID]="
& [attendanceid]) AS PatientName, CDate(PatientObservations.DDate) AS
TDate,
"OBSERVATION" AS [Transaction], PatientObservations.Bill, 0 AS Payment,
PatientObservations.AttendanceID, Null AS ConsultationID, Null AS
PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM PatientObservations
WHERE (((PatientObservations.Bill) Is Not Null))
UNION ALL SELECT Consultation.ConsultationID AS TblID, "CONSULTATION" AS
TblName, DLookUp("[PatientName]","qGroupFromAttendance","[AttendanceID]="
&
[attendanceid]) AS PatientName,
CDate(DLookUp("[DDate]","qGroupFromAttendance","[AttendanceID]=" &
[attendanceid])) AS TDate, "CONSULTATION" AS [Transaction],
Consultation.Bill, 0 AS Payment, Consultation.AttendanceID,
Consultation.ConsultationID, Null AS PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Consultation
WHERE (((Consultation.Bill) Is Not Null))
UNION ALL SELECT Requests.RequestsID AS TblID, "REQUESTS" AS TblName,
DLookUp("[PatientName]","qConsultation","[ConsultationID]=" &
[ConsultationID]) AS PatientName,
CDate(DLookUp("[DDate]","qConsultation","[ConsultationID]=" &
[ConsultationID])) AS TDate, "INVESTIGATION" AS [Transaction],
Requests.Bill,
0 AS Payment, DLookUp("[AttendanceID]","Consultation","[ConsultationID]="
&
[ConsultationID]) AS AttendanceID, Requests.ConsultationID, Null AS
PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Requests
WHERE (((Requests.Bill) Is Not Null))
UNION ALL SELECT Rx.RxID AS TblID, "Rx" AS TblName,
DLookUp("[PatientName]","qConsultation","[ConsultationID]=" &
[ConsultationID]) AS PatientName,
CDate(DLookUp("[DDate]","qConsultation","[ConsultationID]=" &
[ConsultationID])) AS TDate, "DRUG THERAPY" AS [Transaction], Rx.Bill, 0
AS
Payment, DLookUp("[AttendanceID]","Consultation","[ConsultationID]=" &
[ConsultationID]) AS AttendanceID, Rx.ConsultationID, Null AS PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Rx
WHERE (((Rx.Bill) Is Not Null))
UNION ALL SELECT Payments.PaymentID AS TblID, "PAYMENTS" AS TblName,
DLookUp("[PatientGroup]","PatientGroup","[PatientGroupID]=" &
[PatientGroup])
AS PatientName, CDate(Payments.DDate) AS TDate, "PAYMENT" AS
[Transaction], 0
AS Bill, Payments.Amount AS Payment, Null AS AttendanceID, Null AS
ConsultationID, Payments.PaymentID, Payments.PatientGroup
FROM Payments
WHERE (((Payments.Amount) Is Not Null));

The PatientGroup is a table that has PatientGroupID set to Autonumber and
is
the primary field, PatientGroup is a text field, DDate is a date field
with
format dd-mmm-yyyy. Bill and Payment are currency fields. Other tables are
similar, albeit with different names. I included the table names because
they
happen to have the same names as their forms, and I want to be able to
call
up any record for editing with the primary field and the form name.

I appreciate any comments and suggestions you may have on the query in
general and the TDate field in particular.

I should mention that I have not been able to open the databases in the
Smart Access newsletter; a message comes up that I am trying to open a
read-only database etc.

 

[Guest]

Thanks Douglas,

I really appreciate your help; it could not have come at a better time. God
knows how many queries I had assumed were working well when they could be
mistakes waiting to manifest.

I finally figured out why your database did not open; someone forgot to
unzip it!

Thanks too, for your advice about dlookups slowing down things. I will redo
the union query immediately, and God help me for the others I thought should
have been working well.

 

[Pat Hartman]

I agree with Doug completely about the use of DLookup() in queries. They
can almost always be replaced by joins to the lookup tables for far better
performance. Think about it this way - each DLookup() performs a separate
query so if your recordset contains 1000 records, there will be 1000 queries
run behind the scenes to obtain the lookup values whereas if you use a join
to the lookup table, Jet will optimize the way it retrieves the data from
the lookup table by the use of indexes or other means. I was lucky to
discover this very early in my Access days. I had a query that updated
109,000 rows to convert a code value because I was transferring data from
one application to another. I used a DLookup() without even thinking about
it because MS examples used them so frequently. The update took 40 minutes
to complete. I decided that this was way too long even for such a large
recordset and changed the query to join to the lookup table. The result - 3
minutes to process the update!!!

I also think that the DLookup() was the cause of the date field not being
recognized as a date. To test this theory, just remove the union parts that
use the DLookup() to get the date. Jet may assume that DLookup() is a
variant rather than a date because it may return null. I read the new help
entry for DLookup() to see if it said anything about the returned data type
(it didn't) and noticed that MS now recommends that joins be used instead
wherever possible. There has been a tremendous improvement in help content
and the actual search engine in the past year or so.

And one final suggestion regarding dates - NEVER format dates in queries (I
know you didn't) because once you format a date, it becomes a string and
unless the format is yyyymmdd, sorting and comparing will produce erroneous
results.

OK, one more - same thing goes for form controls - if you format the date,
you must enclose the string in pound signs (#) to tell Jet that it is a date
when using the date as criteria in a query.

Thanks again, Douglas.
I think I am getting some answers to my posers after reading Allen
Browne's
article. I am posting the union query nonetheless because I am sure I have
a
lot to gain from your advice. I only fear for my other codes that have
appeared to "work" in some other projects; perhaps some accidents are
waiting
to happen.

Please find herewith the ammended union query that eventually "worked".
The
part that has been of concern is the TDate field that was derived from
DDate:

SELECT PatientGroup.PatientGroupID AS TblID, "PATIENTGROUP" AS TblName,
PatientGroup.PatientGroup AS PatientName, CDate(PatientGroup.DDate) AS
TDate,
"GROUP REGISTRATION" AS [Transaction], PatientGroup.Bill, 0 AS Payment,
Null
AS AttendanceID, Null AS ConsultationID, Null AS PaymentID,
PatientGroup.PatientGroupID AS [Group]
FROM PatientGroup
WHERE (((PatientGroup.Bill) Is Not Null))
UNION ALL SELECT Patients.PatientID AS TblID, "PATIENTS" AS TblName,
Patients.[Surname] & ", " & [OtherNames] AS PatientName,
CDate(Patients.DDate) AS TDate, "PATIENT REGISTRATION" AS [Transaction],
Patients.Bill, 0 AS Payment, Null AS AttendanceID, Null AS ConsultationID,
Null AS PaymentID, Patients.PatientGroup
FROM Patients
WHERE (((Patients.Bill) Is Not Null))
UNION ALL SELECT PatientObservations.ObservationID AS TblID,
"OBSERVATIONS"
AS TblName,
DLookUp("[PatientName]","qGroupFromAttendance","[AttendanceID]="
& [attendanceid]) AS PatientName, CDate(PatientObservations.DDate) AS
TDate,
"OBSERVATION" AS [Transaction], PatientObservations.Bill, 0 AS Payment,
PatientObservations.AttendanceID, Null AS ConsultationID, Null AS
PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM PatientObservations
WHERE (((PatientObservations.Bill) Is Not Null))
UNION ALL SELECT Consultation.ConsultationID AS TblID, "CONSULTATION" AS
TblName, DLookUp("[PatientName]","qGroupFromAttendance","[AttendanceID]="
&
[attendanceid]) AS PatientName,
CDate(DLookUp("[DDate]","qGroupFromAttendance","[AttendanceID]=" &
[attendanceid])) AS TDate, "CONSULTATION" AS [Transaction],
Consultation.Bill, 0 AS Payment, Consultation.AttendanceID,
Consultation.ConsultationID, Null AS PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Consultation
WHERE (((Consultation.Bill) Is Not Null))
UNION ALL SELECT Requests.RequestsID AS TblID, "REQUESTS" AS TblName,
DLookUp("[PatientName]","qConsultation","[ConsultationID]=" &
[ConsultationID]) AS PatientName,
CDate(DLookUp("[DDate]","qConsultation","[ConsultationID]=" &
[ConsultationID])) AS TDate, "INVESTIGATION" AS [Transaction],
Requests.Bill,
0 AS Payment, DLookUp("[AttendanceID]","Consultation","[ConsultationID]="
&
[ConsultationID]) AS AttendanceID, Requests.ConsultationID, Null AS
PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Requests
WHERE (((Requests.Bill) Is Not Null))
UNION ALL SELECT Rx.RxID AS TblID, "Rx" AS TblName,
DLookUp("[PatientName]","qConsultation","[ConsultationID]=" &
[ConsultationID]) AS PatientName,
CDate(DLookUp("[DDate]","qConsultation","[ConsultationID]=" &
[ConsultationID])) AS TDate, "DRUG THERAPY" AS [Transaction], Rx.Bill, 0
AS
Payment, DLookUp("[AttendanceID]","Consultation","[ConsultationID]=" &
[ConsultationID]) AS AttendanceID, Rx.ConsultationID, Null AS PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Rx
WHERE (((Rx.Bill) Is Not Null))
UNION ALL SELECT Payments.PaymentID AS TblID, "PAYMENTS" AS TblName,
DLookUp("[PatientGroup]","PatientGroup","[PatientGroupID]=" &
[PatientGroup])
AS PatientName, CDate(Payments.DDate) AS TDate, "PAYMENT" AS
[Transaction], 0
AS Bill, Payments.Amount AS Payment, Null AS AttendanceID, Null AS
ConsultationID, Payments.PaymentID, Payments.PatientGroup
FROM Payments
WHERE (((Payments.Amount) Is Not Null));

The PatientGroup is a table that has PatientGroupID set to Autonumber and
is
the primary field, PatientGroup is a text field, DDate is a date field
with
format dd-mmm-yyyy. Bill and Payment are currency fields. Other tables are
similar, albeit with different names. I included the table names because
they
happen to have the same names as their forms, and I want to be able to
call
up any record for editing with the primary field and the form name.

I appreciate any comments and suggestions you may have on the query in
general and the TDate field in particular.

I should mention that I have not been able to open the databases in the
Smart Access newsletter; a message comes up that I am trying to open a
read-only database etc.

 

[Glint]

Thanks, Pat.
I will remember your advice.
--
Glint

I agree with Doug completely about the use of DLookup() in queries. They
can almost always be replaced by joins to the lookup tables for far better
performance. Think about it this way - each DLookup() performs a separate
query so if your recordset contains 1000 records, there will be 1000 queries
run behind the scenes to obtain the lookup values whereas if you use a join
to the lookup table, Jet will optimize the way it retrieves the data from
the lookup table by the use of indexes or other means. I was lucky to
discover this very early in my Access days. I had a query that updated
109,000 rows to convert a code value because I was transferring data from
one application to another. I used a DLookup() without even thinking about
it because MS examples used them so frequently. The update took 40 minutes
to complete. I decided that this was way too long even for such a large
recordset and changed the query to join to the lookup table. The result - 3
minutes to process the update!!!

I also think that the DLookup() was the cause of the date field not being
recognized as a date. To test this theory, just remove the union parts that
use the DLookup() to get the date. Jet may assume that DLookup() is a
variant rather than a date because it may return null. I read the new help
entry for DLookup() to see if it said anything about the returned data type
(it didn't) and noticed that MS now recommends that joins be used instead
wherever possible. There has been a tremendous improvement in help content
and the actual search engine in the past year or so.

And one final suggestion regarding dates - NEVER format dates in queries (I
know you didn't) because once you format a date, it becomes a string and
unless the format is yyyymmdd, sorting and comparing will produce erroneous
results.

OK, one more - same thing goes for form controls - if you format the date,
you must enclose the string in pound signs (#) to tell Jet that it is a date
when using the date as criteria in a query.

Thanks again, Douglas.
I think I am getting some answers to my posers after reading Allen
Browne's
article. I am posting the union query nonetheless because I am sure I have
a
lot to gain from your advice. I only fear for my other codes that have
appeared to "work" in some other projects; perhaps some accidents are
waiting
to happen.

Please find herewith the ammended union query that eventually "worked".
The
part that has been of concern is the TDate field that was derived from
DDate:

SELECT PatientGroup.PatientGroupID AS TblID, "PATIENTGROUP" AS TblName,
PatientGroup.PatientGroup AS PatientName, CDate(PatientGroup.DDate) AS
TDate,
"GROUP REGISTRATION" AS [Transaction], PatientGroup.Bill, 0 AS Payment,
Null
AS AttendanceID, Null AS ConsultationID, Null AS PaymentID,
PatientGroup.PatientGroupID AS [Group]
FROM PatientGroup
WHERE (((PatientGroup.Bill) Is Not Null))
UNION ALL SELECT Patients.PatientID AS TblID, "PATIENTS" AS TblName,
Patients.[Surname] & ", " & [OtherNames] AS PatientName,
CDate(Patients.DDate) AS TDate, "PATIENT REGISTRATION" AS [Transaction],
Patients.Bill, 0 AS Payment, Null AS AttendanceID, Null AS ConsultationID,
Null AS PaymentID, Patients.PatientGroup
FROM Patients
WHERE (((Patients.Bill) Is Not Null))
UNION ALL SELECT PatientObservations.ObservationID AS TblID,
"OBSERVATIONS"
AS TblName,
DLookUp("[PatientName]","qGroupFromAttendance","[AttendanceID]="
& [attendanceid]) AS PatientName, CDate(PatientObservations.DDate) AS
TDate,
"OBSERVATION" AS [Transaction], PatientObservations.Bill, 0 AS Payment,
PatientObservations.AttendanceID, Null AS ConsultationID, Null AS
PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM PatientObservations
WHERE (((PatientObservations.Bill) Is Not Null))
UNION ALL SELECT Consultation.ConsultationID AS TblID, "CONSULTATION" AS
TblName, DLookUp("[PatientName]","qGroupFromAttendance","[AttendanceID]="
&
[attendanceid]) AS PatientName,
CDate(DLookUp("[DDate]","qGroupFromAttendance","[AttendanceID]=" &
[attendanceid])) AS TDate, "CONSULTATION" AS [Transaction],
Consultation.Bill, 0 AS Payment, Consultation.AttendanceID,
Consultation.ConsultationID, Null AS PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Consultation
WHERE (((Consultation.Bill) Is Not Null))
UNION ALL SELECT Requests.RequestsID AS TblID, "REQUESTS" AS TblName,
DLookUp("[PatientName]","qConsultation","[ConsultationID]=" &
[ConsultationID]) AS PatientName,
CDate(DLookUp("[DDate]","qConsultation","[ConsultationID]=" &
[ConsultationID])) AS TDate, "INVESTIGATION" AS [Transaction],
Requests.Bill,
0 AS Payment, DLookUp("[AttendanceID]","Consultation","[ConsultationID]="
&
[ConsultationID]) AS AttendanceID, Requests.ConsultationID, Null AS
PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Requests
WHERE (((Requests.Bill) Is Not Null))
UNION ALL SELECT Rx.RxID AS TblID, "Rx" AS TblName,
DLookUp("[PatientName]","qConsultation","[ConsultationID]=" &
[ConsultationID]) AS PatientName,
CDate(DLookUp("[DDate]","qConsultation","[ConsultationID]=" &
[ConsultationID])) AS TDate, "DRUG THERAPY" AS [Transaction], Rx.Bill, 0
AS
Payment, DLookUp("[AttendanceID]","Consultation","[ConsultationID]=" &
[ConsultationID]) AS AttendanceID, Rx.ConsultationID, Null AS PaymentID,
DLookUp("[PatientGroupID]","qGroupFromAttendance","[AttendanceID]=" &
[AttendanceID]) AS [Group]
FROM Rx
WHERE (((Rx.Bill) Is Not Null))
UNION ALL SELECT Payments.PaymentID AS TblID, "PAYMENTS" AS TblName,
DLookUp("[PatientGroup]","PatientGroup","[PatientGroupID]=" &
[PatientGroup])
AS PatientName, CDate(Payments.DDate) AS TDate, "PAYMENT" AS
[Transaction], 0
AS Bill, Payments.Amount AS Payment, Null AS AttendanceID, Null AS
ConsultationID, Payments.PaymentID, Payments.PatientGroup
FROM Payments
WHERE (((Payments.Amount) Is Not Null));

The PatientGroup is a table that has PatientGroupID set to Autonumber and
is
the primary field, PatientGroup is a text field, DDate is a date field
with
format dd-mmm-yyyy. Bill and Payment are currency fields. Other tables are
similar, albeit with different names. I included the table names because
they
happen to have the same names as their forms, and I want to be able to
call
up any record for editing with the primary field and the form name.

I appreciate any comments and suggestions you may have on the query in
general and the TDate field in particular.

I should mention that I have not been able to open the databases in the
Smart Access newsletter; a message comes up that I am trying to open a
read-only database etc.