vba access openreport where condition syntax

[issactang]

i am trying to check
if any patrons has not returned their books yet.

so I would check as follows:
1. check if checked in date is null
2. check if today > duedate ( that means overdue)
the syntax was swtich to duedate < today


my code

 
Dim strdate As String
Dim nowdate As String
nowdate = Format(Now(), "short date")
strdate = "[duedate]=" & nowdate
Me.today = nowdate
   DoCmd.OpenReport "transactions", acViewPreview, , "[Checked in date] Is Null" And "& #[duedate]# < & me.today&"
 

it says type mismatch, which part did I wrote wrongly ?
I am hopeless in syntax..

[Stewart Ross]

Quite a mix of problems in line 7. With a line break and some braketing added for clarity, line 7 corrected is:

DoCmd.OpenReport "transactions", acViewPreview, , _
"([Checked in date] Is Null) And ([duedate] < #" & me.today & "#)"

The pound symbols (#) are needed only where you refer to a date literal (such as the value of your control me.today), not to the name of an existing field of type date/time.

-Stewart

[issactang]

thanks for the correction , but it said ,
microsoft access cannot find the field "|" referred in your expression.

[Stewart Ross]

WIthout knowing in what context the error message you quote occured I can't easily comment. I cannot see any reason why this would be related to the correction I suggested, so I suspect it is something else entirely which is responsible.

Have you tried compiling the module (from the VBA IDE)? If it compiles successfully, on what line in your code does the error occur? Does control me.today contain a relevant value when the OpenReport method is called? Can you run the report itself as normal just from the Access window with no errors?

Please note that there is a Date() function which should be used in place of the Now() function you have in your code. Now() includes the time - Date() is just the current date. In addition, I think your where clause should really just be referring to the current date

DoCmd.OpenReport "transactions", acViewPreview, , _
"([Checked in date] Is Null) And ([duedate] < #" & Date & "#)"

I would also like to see the code for your subroutine as you now have it, to rule out transcription errors causing further issues.

-Stewart

[issactang]

Private Sub Command75_Click()
On Error GoTo Err_Command75_Click
Dim strdate As String
Dim nowdate As String
'nowdate = Format(Date, "short date")
Me.today = Date
 
DoCmd.OpenReport "transactions", acViewPreview, , _
"([Checked in date] Is Null) AND ([duedate] < #" & Date & "#)"
 
'DoCmd.OpenReport "transactions", acViewPreview, , _
'"([Checked in date] Is Null) And ([duedate]) >= #" & Me.today & "#"
 
 
 
 
 
 
Exit_Command75_Click:
    Exit Sub
 
Err_Command75_Click:
    MsgBox Err.Description
    Resume Exit_Command75_Click
 
End Sub
 

that is my original code, which is an event after hte click.
thanks for reminding my the now() and date ().
it goes back to the issue of my tables.
thew field [due date] is set to default now()+7
so when I type

DoCmd.OpenReport "transactions", acViewPreview, , _
"([Checked in date] Is Null) And ([duedate]) >= #" & Me.today & "#"

there was no results.
now since the current date and the field date is finally in the same date format, I can now find out which patrons have not return overdue books.
thanks a lot!!!

[issactang]

i have another question is that is it possible to make a field in the report which can calculate the fine until today ?

my flow is
if overdue
then
calculate overdue fine
show it in the report

pseudocode:

if today > duedate then
(today-duedate)*overduerate = fine
 
 
 

show accumulated fine ( up to today in the report which is not from tables)
is it possible if so how should i do , where to add ?

thanks again with your warm advice, patience and efforts.

[Stewart Ross]

If you are working with dates you can just subtract one from the other to get the number of days difference between them. Just as in your pseudocode, you can multiply that number of days by the fine per day to get the overall amount payable:

(Date - [duedate]) * overduerate

As to where you would put this - well, it could be included on a form or a report, but if I was preparing this one I'd want to take a more general approach. Instead of having to work with where clauses and so on, I'd include a calculated field in the base query of your report which simply returns True if the item is overdue, and false if not. In SQL terms, this is just something like

([checked in date] IS NULL) AND ([DueDate] >= Date()) AS Overdue

I'd also include the date difference:

Date() - [DueDate] as DaysDiff

You can then test for the item being overdue by testing the value of the field and then include the calculation itself wherever necessary:

IIF([Overdue], [DaysDiff] * [Overduerate], 0) as Fine

You would have to include the overduerate in the query concerned, of course. I would suggest that this should be stored in a table so that it can be changed whenever necessary, as fine rates will vary over time.

Good luck with your experiments on this one.

-Stewart

[issactang]

i wrote the following

 
DoCmd.OpenReport "transactions", acViewPreview, , _
"(#" & Date & "# > [duedate]  )" & " And " & " ([Checked in date] Is Null)"
 

not syntax error but run-time error
it shows me only all patrons that have not returned the book but not for those who have overdue and not yet return the book.
I have made sure in table - transactions, the field duedate is set to time/date and it's type is shortdate.

in the report I have no idea why it does not show the via short date bur rather medium date.

i tested with the code only with the where condition if
today > duedate but still it is able to display all overdue dates but those books are resolved, (returned)
I have one record which have normal duedate
date is 31/1/2012
another one is 31/1/2011
where as today's date is 6/1/2012

my expectation is to show only the 31/1/2011 one.

I have also tested the another part of the where condition,
where

which part did i went wrong ?
thanks.,

[Stewart Ross]

If you could zip a copy of your database and attach it to your next post I'd be happy to have a look at it for you. To zip it from Windows, right-click on the file and choose Send To, Compressed (zipped) Folder. To attach the zipped folder to your post select Advanced (beside the Post Reply button) and then choose Manage attachments. Browse for the folder concerned and then Upload the folder to attach it to your post.

-Stewart

[issactang]

stewarts, thanks a lot for your help and patience.
I am sorry that due to the tables contain confidential information of patients.
I can give you printscreens.

[Stewart Ross]

The images help a little, but they cannot assist me in working out what is actually happening with your data.

On line 3 in your last post I note you are still using the pound signs and incorporating the value of the Date function as a date literal. There is no need to do so when comparing date/time types directly, and indeed it is better not to.

One of the things that I have not mentioned so far is that if you are going to compare date literal values they need to be in m/d/y format, not d/m/y. The reason is that ANSI SQL and its variants use the American date form, not those from other countries. Access itself is good at converting dates behind-the-scenes, but the comparison of literals can cause real problems unless it is recognised that the format compared should be m/d/y. I am wondering if this is the source of your OpenReport filter problems.

Anyhow, line 3 of your second last post should really just be:

"(Date() > [duedate]) And ([Checked in date] Is Null)"

VBA will remove the function brackets from Date() when you enter it, leaving it just as Date.

Dates are actually stored as a whole-number count of days internally, referred to an arbitrary date of 1 Jan 1900. When the database engine compares two date/time types it is actually comparing numbers, which is a straightforward task. When one of the values is a date literal the database engine must convert the value to a day number, and the convention followed is the ANSI SQL one that dates are in m/d/y form before conversion.

If you still want to use the literal version as per line 3 of your last post, it should really be formatted into m/d/y form like this:

"(#" & Format(Date, "mm/dd/yyyy") & "# > [duedate] And ([Checked in date] Is Null)"

I understand that you cannot post the DB concerned because of confidential information being present. If it was possible to remove such information from a test version, so that you were only attaching the directly-relevant parts of the DB, this would help.

I can offer no further suggestions at present without being able to see what is actually happening with your data.

-Stewart

[issactang]

stewart.
thanks alot for the pound sign reminder.
after removing it I got what I want.
thanks a lot.!!

this worked:

"(Date() > [duedate]) And ([Checked in date] Is Null)"
 

It is the really practice makes perfect.
thanks again for the detailed information on ansi sql and date formats and your patience.
they are good reference for me. ;)