473,836 Members | 1,324 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

vba access openreport where condition syntax

25 New Member
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
11 9660
Stewart Ross
2,545 Recognized Expert Moderator Specialist
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
25 New Member
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 Recognized Expert Moderator Specialist
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
25 New Member
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
25 New Member
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 Recognized Expert Moderator Specialist
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
25 New Member
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 Recognized Expert Moderator Specialist
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
25 New Member
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, 120 views)
Jan 6 '12 #10

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

Similar topics

by: Arnold | last post by:
Hi, I'm using visual studio .NET 2003 enterprise and MySQL 5.0. I've created a database project in wich I like to create scripts for stored procedures and queries. I've created a connection using MyODBC 3.5.1 drivers and microsoft OLE DB provider for ODBC drivers as well as an OLE driver specificly for MySQL.
by: Aaron | last post by:
Hello, I'm trying to limit a report to a list of parameters that I pass in. The report and the underlying query both use the column FirstName. Whenever I try to limit the FirstName in via the "Where" clause, I get prompted to input the FirstName parameter, even though I'm passing it in via OpenReport The syntax I tried using:
by: seanseaghan | last post by:
New to this group, so greetings all! I am trying to develop query syntax in Access 2000 to accomplish the following: Imagine you are in an accounting dept. and you are working on a reconiliation of debits and credits. Say you have a table / list that is 1000s of lines long. These could be either debits or credits. Then imagine you know that a certain
by: John Øllgård Jensen | last post by:
Hi Using MS Asccess 2000: In a query I'm trying to create a new field with following expression: FilmDate: Left(,4) The field "FilmNo" is another text field in the query. This is expression should return the 4 leftmost characters of the FilmNo
by: Forest14 | last post by:
Hello, I have been trying to solve this issue for long and need help from you guys! Here is the point: Looking to get the following SQL into VBA: SELECT Securities.Name, Securities.ISIN FROM Securities WHERE (((Securities.Name) Like !! & "*"))
by: Urs Thuermann | last post by:
With offsetof() I can get the offset of a member in a struct. AFAICS, it is portable and clean to use this offset to access that member. I need to do something like this struct foo { struct foo *next; int a; int b; int c; };
by: Fary4u | last post by:
Hi i've got this following error any body knows how to overcome this coz it's waste my 3 hours Error Type: Microsoft VBScript compilation (0x800A03F6) Expected 'End' line 58 is my if condition have something wrong ?
by: gavm360 | last post by:
Hello, im trying to open a form from an dialog box form: the button on the dialog box has this on the onclick event: DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , "MCH_CASECODE = #" & Me!txtCCODE & "#" the form I'm trying to open is "frmcases_union" and im trying to filter it by the "MCH_CASECODE" which i type into
by: benjen | last post by:
Hi all, XP, office 2003 Pro, MS access. Fairly newby. I'd like to run simple calculations on multiple and specific colums like avg and sd. The problem is that the empty cells cause the functions avg, sum et cetera to fail. Even a simple sum fails, if one of the columns has an empty cell. The cells are blank, because a certain parameter has been measured, but has given no response. Entering zero is not possible, since likely number....
by: ckrows | last post by:
I have worked with Access for awhile but never had to send any emails out as a result of a field. The situation I have is as follows, if a dollar amount is over $1000.00 an email generates to a specific email address. The address is static, I would like to include the name of the client and the dollar amount in the email. I read through a couple of threads and added the Outlook.Application library but not sure how to accomplish the rest....
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.