469,300 Members | 2,050 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,300 developers. It's quick & easy.

vba access openreport where condition syntax

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
Expand|Select|Wrap|Line Numbers
  2. Dim strdate As String
  3. Dim nowdate As String
  4. nowdate = Format(Now(), "short date")
  5. strdate = "[duedate]=" & nowdate
  6. Me.today = nowdate
  7.    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..
Jan 5 '12 #1

✓ answered by Stewart Ross

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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "transactions", acViewPreview, , _
  2. "([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.


11 8771
Stewart Ross
2,545 Expert Mod 2GB
Quite a mix of problems in line 7. With a line break and some braketing added for clarity, line 7 corrected is:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "transactions", acViewPreview, , _
  2. "([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.

Jan 5 '12 #2
thanks for the correction , but it said ,
microsoft access cannot find the field "|" referred in your expression.
Jan 5 '12 #3
Stewart Ross
2,545 Expert Mod 2GB
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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "transactions", acViewPreview, , _
  2. "([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.

Jan 5 '12 #4
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command75_Click()
  2. On Error GoTo Err_Command75_Click
  3. Dim strdate As String
  4. Dim nowdate As String
  5. 'nowdate = Format(Date, "short date")
  6. Me.today = Date
  8. DoCmd.OpenReport "transactions", acViewPreview, , _
  9. "([Checked in date] Is Null) AND ([duedate] < #" & Date & "#)"
  11. 'DoCmd.OpenReport "transactions", acViewPreview, , _
  12. '"([Checked in date] Is Null) And ([duedate]) >= #" & Me.today & "#"
  19. Exit_Command75_Click:
  20.     Exit Sub
  22. Err_Command75_Click:
  23.     MsgBox Err.Description
  24.     Resume Exit_Command75_Click
  26. 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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "transactions", acViewPreview, , _
  2. "([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!!!
Jan 5 '12 #5
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
calculate overdue fine
show it in the report


Expand|Select|Wrap|Line Numbers
  1. if today > duedate then
  2. (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.
Jan 5 '12 #6
Stewart Ross
2,545 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. (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

Expand|Select|Wrap|Line Numbers
  1. ([checked in date] IS NULL) AND ([DueDate] >= Date()) AS Overdue
I'd also include the date difference:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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.

Jan 5 '12 #7
i wrote the following
Expand|Select|Wrap|Line Numbers
  2. DoCmd.OpenReport "transactions", acViewPreview, , _
  3. "(#" & 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,

which part did i went wrong ?
Jan 6 '12 #8
Stewart Ross
2,545 Expert Mod 2GB
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.

Jan 6 '12 #9
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.
Attached Files
File Type: zip vba_openreport_problem.zip (697.4 KB, 114 views)
Jan 6 '12 #10
Stewart Ross
2,545 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. "(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:

Expand|Select|Wrap|Line Numbers
  1. "(#" & 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.

Jan 6 '12 #11
thanks alot for the pound sign reminder.
after removing it I got what I want.
thanks a lot.!!

this worked:
Expand|Select|Wrap|Line Numbers
  1. "(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. ;)
Jan 6 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by John Øllgård Jensen | last post: by
6 posts views Thread by Urs Thuermann | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.