I'm using Dlookup to search a table for the first record it comes across that has a date >= the date i specifiy.
e.g. - formateddate = format(Me.SelectedDate,"mmddyy")
-
-
VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >= #" & formateddate & "#")
The problem i have is that there maybe several records in tbChargeRate that have PKDateField > Me.SelectedDate . But i only want the first one it comes across, which is what i thought DLookup did.It does not act as i think it should.
I have four test records in tbChargeRate with PKDateField
Record1 : 01/08/06
Record2 : 01/09/06
Record3 : 01/04/07
Record4 : 07/06/08
These are some test results
If i use Me.SelectedDate as 01/05/07 DLookup returns Record1(01/06/07) correct
If i use Me.SelectedDate as 01/01/05 DLookup returns Record1(01/04/07) wrong
if i use Me.SelectedDate as 01/05/07 DLookup returns Record1(07/06/08) correct
As you can see when using 01/01/05 should return Record1, but instead it returns Record3.
I'm very confused on whats going on here, any suggestions and help welcome.
3 2566 ADezii 8,834
Recognized Expert Expert
I'm using Dlookup to search a table for the first record it comes across that has a date >= the date i specifiy.
e.g. - formateddate = format(Me.SelectedDate,"mmddyy")
-
-
VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >= #" & formateddate & "#")
The problem i have is that there maybe several records in tbChargeRate that have PKDateField > Me.SelectedDate . But i only want the first one it comes across, which is what i thought DLookup did.It does not act as i think it should.
I have four test records in tbChargeRate with PKDateField
Record1 : 01/08/06
Record2 : 01/09/06
Record3 : 01/04/07
Record4 : 07/06/08
These are some test results
If i use Me.SelectedDate as 01/05/07 DLookup returns Record1(01/06/07) correct
If i use Me.SelectedDate as 01/01/05 DLookup returns Record1(01/04/07) wrong
if i use Me.SelectedDate as 01/05/07 DLookup returns Record1(07/06/08) correct
As you can see when using 01/01/05 should return Record1, but instead it returns Record3.
I'm very confused on whats going on here, any suggestions and help welcome.
The Format() Function returns a Variant of Type String which means you may be comparing a String Value with leading and trailing # signs. Explicitly Declare formateddate as a Date Variable, then it should be able to be coerced to a Date for comparisons: - Dim formateddate As Date
-
formateddate = format(Me.SelectedDate,"mmddyy")
-
-
VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >= #" & formateddate & "#")
The Format() Function returns a Variant of Type String which means you may be comparing a String Value with leading and trailing # signs. Explicitly Declare formateddate as a Date Variable, then it should be able to be coerced to a Date for comparisons: - Dim formateddate As Date
-
formateddate = format(Me.SelectedDate,"mmddyy")
-
-
VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >= #" & formateddate & "#")
If i declare formateddate as a Date type, it gives a run time error - "type mismatch"
ADezii 8,834
Recognized Expert Expert
If i declare formateddate as a Date type, it gives a run time error - "type mismatch"
- Are you sure [PKDateField] is defined as a Date Data Type in the Table?
- If it is, and you're still getting the Error, try the conversion within DLookup() as in:
- VarX = DLookup("[PKDateField]", "tbChargeRate", "[PKDateField] >= #" & CDate(formateddate) & "#")
- This is strange since I had no trouble obtaining the proper results.
- Let me know how you make out.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Sue |
last post by:
I sent an e-mail below and got a responese but i still have the #error
message in the subject below: Can someone help me: questions and
replies are separted by -----
-------My Questions:
I have a form with a field that automatically popluates with todays
date
called "created date". I have another field on the form called
"Processing
Month1" that I want to update automatically base on a schedule in a
|
by: ShyGuy |
last post by:
I have a table with 4 fields. Three are used for criteria.
I can get the DLookup to work with 1 criteria with the following but
can't get it to work with 2 or three.
NumofAppts = DLookup("", "LookUpAppts", "
= " & Forms!!NumofPeople)
Can someone tell me how to add multiple criteria? I tried "And" but
it doesn't seem to work.
|
by: thx606 |
last post by:
Hello,
I'm having problems figuring this one out. I have 2 combo boxes. The
first one the user selects a professor then the second one they select
a course and that works fine. Here is my problem for a third field used
for mailing instructions. I would like to look this up in a seperate
table based off the values entered in the combo boxes. So, if
professor and Course match then that mailing instructon would appear in
the third field....
|
by: Colin Mardell |
last post by:
Can anyone tell me why this VBA returns a null value when the source
table doesn't have one?
I have a table called 'zTerms' with fields 'TermID', 'Term',
'TermStartDate' and 'TermEndDate'
I need to return the TermID when the IncidentDate shown on the form is
between the the two dates.
I have tried the following methods all to no avail.
|
by: Christine Henderson |
last post by:
I have a problem using the above function in the following simplified
circumstance:
In the lookup table called "Klms Travelled" I have 3 fields, eg:
Receiver Name Receiver Suburb Klms Distance
Jones Melbourne 500
Harrison Sydney 200
Ford Brisbane 700
Jones ...
| |
by: bjaj |
last post by:
Hi
How do I use a boolean criterian with the funktion DLookup ?
I know the syntax for strings, numeric and date as follows
For numerical values:
DLookup("FieldName" , "TableName" , "Criteria = n")
For strings: (note the apostrophe before and after the value)
|
by: BobM |
last post by:
i read the thread entitled "Convert Field Value String to Actual Field in Expression"
It appeared that the final solutions got moore complex not less complex
MY PROBLEM IS:
1. get a value from one table as a string
2. lookup the value of a field in a second table
where the field name = the value of field in table 1
I CAN GET THIS USING DLOOkUP, BUT NOT BY FIND METHOD
|
by: boyleyc |
last post by:
Hi all
the following code works perfectly well. Basically it populates a
series of check boxes on my form, depending on whether dlookup finds an
associated record.
The problem i have is that when the date (or xDate) goes over to the
next month, dlookup returns NULL even though though there is a record.
For example if CALWEEKSTART is 26/11/06 then the loop will check for
|
by: JHNielson |
last post by:
I have a query that I'm trying to update with a dlookup with multiple criteria
This is the string:
EVNT_DT: DLookUp("","","( .EVNT_QTR=.) & (.=.)")
When i run it it says it can't find the name PRC_15011 - Find_Vendor_Mgmt_Fees.EVNT_QTR.
But I know the names are right, I've checked them three times.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: 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: 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: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |