multiple criteria dlookup troubles vba access 2010(accdb)

[Rinis]

I'm working on a timeclock system in a vba access database. I'm trying to get the system setup so when you hit the clock in button it checks to see if you have clocked in at some point that day. if you have it displays a message telling you you have already clocked in today. I am trying to look up two values. it first checks for the employee id and the second looks up the date today.

I'm using a composite key with the employee ID and the date as the primaries. I was working on this for several hours yesterday and just cannot find my mistake in this dlookup.

everything is setup as strings because of a screw up i made earlier in the database and did not want to have to go back through it. The EmpID is setup as string. The Date is currently as string but if it needs to be changed I'm sure that wouldn't cause too much of an issue.

Anyway I'm getting a null value off of this dlookup so i'm sure my syntax is wrong on some level. Any help evaluating this dlookup would help.

Actual names of variables are as follows

strEmpID as string
Datetoday as string

lastly in this last attempt i was trying to take the direct input from the combo box on the form.

 strNameAndDate = DLookup("[EmpID]", "TblTimeSlip", "[EmpID] = " & Forms![ClockingService]![CboEmpID] And "[TblTimeSlip]![DateWorked] =" & strDateToday)

any help is incredibly appreciated thank you

Dave

[Seth Schrock]

The And in your criteria needs included inside your double quotes and then have an ampersand placed there in addition to the word And.

 strNameAndDate = DLookup("[EmpID]", "TblTimeSlip", "[EmpID] = " & Forms![ClockingService]![CboEmpID] & " And [TblTimeSlip]![DateWorked] =" & strDateToday)

[Rinis]

Sorry it took me so long to reply. With labor day weekend and all I've been busy. I tried what you put down but it is still returning a null value. The [DateWorked] Field is formatted as short date though. Could that have something to do with it? maybe its checking for a more specific value so it's returning a null value? any input given would be fantastic.

[Seth Schrock]

Try this:

Dim strCriteria As String
strCriteria = "[EmpID] = " & Forms![ClockingService]![CboEmpID] & " And [TblTimeSlip]![DateWorked] =" & strDateToday
Debug.Print strCriteria

strNameAndDate = DLookup("EmpID" "TblTimeSlip", strCriteria)

When your code runs, it will print what your criteria actually is (with the proper values inserted) in the immediate window (to open it press Ctrl+G). Please run your code and then copy and paste what got printed into this thread.

Also, I just noticed that the field that you are trying to return (EmpID) is also in your criteria. I personally can't think of a situation where you would need this since the only value that could come back is either a null or the value that is in CboEmpID.

[Rinis]

Maybe I'm just not quite understanding that part of the function is for. I was using it as a comparison I guess. I know that the only way i will be able to get a unique field from [TblTimeSlip] is to combine the date and the [EmpID] field. The dlookup is actually being used to check and see if the employee has clocked in yet today. That way the employee cannot accidentally clock in again and overwrite their click in time with a later one.

It's now giving me a data type mismatch. error number 3464. I'm sorry i'm being such a pain but I'm so confused with this dlookup function.

[Seth Schrock]

When you get the error message, click debug and then tell us which line is highlighted. I'm suspecting that EmpID is a number field and the combobox might be bound to a text field which would cause the error, but that is only a guess at this point.

[Rinis]

strNameAndDate = DLookup("EmpID" , "TblTimeSli p", strCriteria)

that is exactly where it is being highlighted but i have everything set as a string. The reason I did it this way was because of a screw up earlier on in the setup of the database that I thought would be easier to deal with with strings.

The criteria statement is working fantastically. It is returning this exact value:

[EmpID] = 5 And [TblTimeSlip]![DateWorked] =9/3/2013
8/29/2013

Could it be returning null and throwing the error because it names the fields when it is looking for the record? so it never finds anything because of the extra characters in there?

the Combobox has no control source and its row source is the table EmployeeInfo which contains their name address ssn etc.

[Seth Schrock]

Try changing strCriteria to

strCriteria = "[EmpID] = " & Forms![ClockingService]![CboEmpID] & " And [DateWorked] = #" & strDateToday & "#"

Also, can you post how you are assigning the dates to strDateToday? There can only be one date in the criteria and dates need to be surrounded by pound signs. I also don't know if it works to have the table name in front of the field as I have never tried it. Let me know what you come up with.

[Rinis]

All variables are strings currently, including strDateToday.
I'm assigning the date to strDateToday with

strDateToday = Format(Date, ddmmyyyy)

I tried changing strCriteria to

strCriteria = "[EmpID] = " & Forms![ClockingService]![CboEmpID] & " And [DateWorked] = #" & strDateToday & "#"

the immediate window shows:
strCritera is = [EmpID] = 5 And [DateWorked] = #9/3/2013#

the dlookup is giving a null still with the same error code. I'm thinking that I'm probably setting the date incorrectly. Once again thank you so much for your help seth.

[zmbd]

For trouble shooting....
Open a new query based on the table you are trying to pull this record from ( "TblTimeSli p" ).
Add the fields you are trying to return the value from... so it looks like at least, [EmpID] and [Dateworked].
In the criteria section of the QDE, enter the "5" and "#9/3/2013# in the [EmpID] and [Dateworked] respectively.

1) Right click in an empty part of the section that shows the tables, select SQL view. Please copy the text that shows and post it back here, please format that text using the [CODE/] button.
2) Run the query, report back how many records it returns and if there are any returned records, are these records correct for the criteria entered?

[Rinis]

The following is the code needed in SQL.

SELECT TblTimeSlip.EmpID, TblTimeSlip.DateWorked
FROM EmployeeInfo INNER JOIN TblTimeSlip ON (EmployeeInfo.EmpID = TblTimeSlip.EmpID) AND (EmployeeInfo.EmpID = TblTimeSlip.EmpID)
WHERE (((TblTimeSlip.EmpID)="5") AND ((TblTimeSlip.DateWorked)=#9/3/2013#));

It did return exactly what I wanted it to. It only reported back one which is the record I created to have it check for. It returned a 5 for [EmpID] and 9/3/2013 for the [DateWorked] Field.

[Rinis]

Well I've been messing with this same dlookup function all day today and still cannot figure it out. Is this a lost cause? there has to be something I'm doing wrong in this function.

[Rabbit]

It looks like your EmpID is a text field. You need to account for that in your DLookup by surrounding the value it's looking for in quotes like you do in your query.

[Rinis]

I'm not sure if that helped though I feel like it did. I've tried it a couple of different ways. I currently have:

strNameAndDate = DLookup("EmpID", "TblTimeSlip", "[EmpID] = " & "strFoundID" And "[TblTimeSlip]![DateWorked] =" & "strDateToday")

The above code is giving me an "error code 13 type mismatch".
I have all of the variables as strings currently. The table format however is "short date". Would this have an effect?

I've also tried:

strNameAndDate = DLookup("EmpID", "TblTimeSlip", "[EmpID] = " & strFoundID And "[TblTimeSlip]![DateWorked] =" & strDateToday)

this returns the same error code. I'm sorry i'm being such a crutch but its something i gotta figure out one way or another.I'm sure the knowledge will come in handy, either on this another project later or later on during this project.

I'm about to head home from work now so I probably won't get a chance to reply today, but this will be the first thing I touch in the morning so you should have a reply first thing in the morning. Thanks again so much for your help. If you need any more information feel free to ask.

[Rabbit]

Try this:

strNameAndDate = DLookup("EmpID", "TblTimeSlip", "[EmpID] =  """ & strFoundID & """ And [DateWorked] = #" & strDateToday & "#")