?
Solved

DLOOKUP syntax

Posted on 2004-09-21
42
Medium Priority
?
1,032 Views
Last Modified: 2012-06-27
Hi guys

(From: www.experts-exchange.com/Databases/MS_Access/Q_21138787.html)

I have a problem with a DLOOKUP statement.

This is the statement I have at the moment:

DLookup("[recordID]", "tblRecords_Shift_Details", "[Advisor] =" & Chr(34) & Me.cboAdvisor & Chr(34) & " And [Date_of_month] =  #" & DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1) & "#")

If I select "9" in cboMonth and "Joe Bloggs" from cboAdvisor this should return any records that match "Joe Bloggs" and "1/9/2004" in my table.

However, it keeps throwing errors for syntax or "invalid use of null" when I try and show the RecordID in a msgbox command.

What I want to do is use an IF statement to either INSERT or UPDATE the value into teh table. ie:

(pseudo)

DLOOKUP - check if record exists

If Dlookup = true then
sSQL = "INSERT INTO tblRecords_Shift_Details ( Month_Number, Advisor, Shift_Status, Experience_Level, Date_of_Month ) SELECT [Forms]![frmAdmin_Shifts]![cboMonth], [Forms]![frmAdmin_Shifts]![cboAdvisor], [Forms]![frmAdmin_Shifts]![cbo01_Sh], [Forms]![frmAdmin_Shifts]![cbo01_Ex], DateSerial(2004,Val([Forms]![frmAdmin_Shifts]![cboMonth]),1);"
else
sSQL = "Update into blah blah"
end if

Docmd.Runsql sSQL

Any help, please.....?
0
Comment
Question by:dannywareham
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 10
  • 8
  • +2
42 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12113595
Dim recID As Variant

recID=DLookup("[recordID]", "tblRecords_Shift_Details", "[Advisor] =" & Chr(34) & Me.cboAdvisor & Chr(34) & " And [Date_of_month] =  #" & DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1) & "#")

If IsNull(recID) Then
   'Dlookup found no match
Else
   'Do something else
End If

DLookup returns a null value if it finds no match, so you must either assign the result into a Variant data type and/or use IsNull to test for this condition.
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12113599
Is it possible that your dlookup return no records?

If it doesnt find anything matching your criteria, then it will return an error, and the message box will not be able to return any results.

You may want to first make sure that your dlookup actually finds some data, and only if it does, then run the update qry
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12113628
JonoBB - I know

I'll use an errortrap to make sSQL the insert statement...

:-)  

Shane - I'll give it a whirl (thanks)
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 8

Expert Comment

by:JonoBB
ID: 12113663
Your trap will not work for null values...

Shane beat me to the right solution on this one!
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12113676
When I referred to "Your trap", I was referring to

Dlookup = true

0
 
LVL 26

Author Comment

by:dannywareham
ID: 12113698
Shane - It's still not finding the record.

There's definately a record that matches the criteria
I've checked that "DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1)" returns "1/9/2004" - no probs

You must be getting as tired of these as me..  :-)
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12113708
Jono - I thought that NULL would be trapped as normal
Another thing that I've learned today...!

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12113710
Make sure you format the date in US format:

DLookup("[recordID]", "tblRecords_Shift_Details", "[Advisor] =" & Chr(34) & Me.cboAdvisor & Chr(34) & " And [Date_of_month] =  #" & Format(DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1),"mm/dd/yyyy") & "#")
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12113726
try

DLookup("[recordID]", "tblRecords_Shift_Details", "[Advisor] =" & Me.cboAdvisor & " And [Date_of_month] =  #" & DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1) & "#")

Also, is frmAdminShifts open at the time you run this?
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12113744
Ah ha...

US formatting worked - although I'm unsure as to why (I'm in UK and all my settings are UK)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12113768
Jet expects date formats to be in US format - the only place you can use your regional format is in the query design grid. Everywhere else, you MUST explicitly format it to US format.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12113792
What about:

DLookup("[recordID]", "tblRecords_Shift_Details", "[Advisor] =" & Chr(34) & Me.cboAdvisor & Chr(34) & " And [Date_of_month] =  DateValue(""" & "2004/" & ([Forms]![frmAdmin_Shifts]![cboMonth]) & "/15 & """)-14")
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12113803
Oops, to late...
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12113900
It's OK Sam - still a little more to do around the UPDATE part...

The final code that I have is:

Dim recID As Variant
recID = DLookup("[recordID]", "tblRecords_Shift_Details", "[Advisor] =" & Chr(34) & Me.cboAdvisor & Chr(34) & " And [Date_of_month] =  #" & Format(DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1), "mm/dd/yyyy") & "#")
If IsNull(recID) Then
   'Dlookup found no match
   sSQL = "INSERT INTO tblRecords_Shift_Details ( Month_Number, Advisor, Shift_Status, Experience_Level, Date_of_Month ) SELECT [Forms]![frmAdmin_Shifts]![cboMonth], [Forms]![frmAdmin_Shifts]![cboAdvisor], [Forms]![frmAdmin_Shifts]![cbo01_Sh], [Forms]![frmAdmin_Shifts]![cbo01_Ex], DateSerial(2004,Val([Forms]![frmAdmin_Shifts]![cboMonth]),1);"
Else
   'Do something else
    sSQL = "UPDATE  SET [Forms]![frmAdmin_Shifts]![cboAdvisor] = [tblRecords_Shift_Details]![Advisor], [Forms]![frmAdmin_Shifts]![cbo01_Ex] = [tblRecords_Shift_Details]![Experience_Level], [Forms]![frmAdmin_Shifts]![cbo01_Sh] = [tblRecords_Shift_Details]![Shift_Status], [Forms]![frmAdmin_Shifts]![cboMonth] = [tblRecords_Shift_Details]![Month_Number];"
End If

DoCmd.RunSQL sSQL

The UPDATE sSQL is incomplete, but I can't get my query builder to accept the DateSerial part, and am unsure of the WHERE criteria.
It simply needs to match RecID...

0
 
LVL 26

Author Comment

by:dannywareham
ID: 12113953
I've tried to work through it

Does this look about right...?

UPDATE  SET [Forms]![frmAdmin_Shifts]![cboAdvisor] = [tblRecords_Shift_Details]![Advisor], [Forms]![frmAdmin_Shifts]![cbo01_Ex] = [tblRecords_Shift_Details]![Experience_Level], [Forms]![frmAdmin_Shifts]![cbo01_Sh] = [tblRecords_Shift_Details]![Shift_Status], [Forms]![frmAdmin_Shifts]![cboMonth] = [tblRecords_Shift_Details]![Month_Number], DateSerial(2004, val([Forms]![frmAdmin_Shifts]![cboMonth]),1) = [tblRecords_Shift_Details]![Date_of_Month]
WHERE
RecID = [tblRecords_Shift_Details]![RecordID]
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12113961
Using the "DateValue(""" & "2004/" & ([Forms]![frmAdmin_Shifts]![cboMonth]) & "/15 & """)-14" that I posted above gets around the date serial by using the DateValue Function to convert "2004/9/15" to date then subtract 14. I know it is an odd work around, but it removes any local formating problems by giving the Query clear Day, Month & Year numbers regardless of current settings.
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12113967
Nope - syntax error
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12113987
You need a table name in your Update statement. Something like:

"UPDATE tablename SET ..."
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12114010
UPDATE tblRecords_Shift_Details SET [Forms]![frmAdmin_Shifts]![cboAdvisor] = [tblRecords_Shift_Details]![Advisor], [Forms]![frmAdmin_Shifts]![cbo01_Ex] = [tblRecords_Shift_Details]![Experience_Level], [Forms]![frmAdmin_Shifts]![cbo01_Sh] = [tblRecords_Shift_Details]![Shift_Status], [Forms]![frmAdmin_Shifts]![cboMonth] = [tblRecords_Shift_Details]![Month_Number], DateSerial(2004, val([Forms]![frmAdmin_Shifts]![cboMonth]),1) = [tblRecords_Shift_Details]![Date_of_Month]
WHERE recID = [tblRecords_Shift_Details]![RecordID];

Still has syntax error....  :-(
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12114081
I re-aranged the syntax a little:

UPDATE tblRecords_Shift_Details SET [tblRecords_Shift_Details]![Advisor]=[Forms]![frmAdmin_Shifts]![cboAdvisor], [tblRecords_Shift_Details]![Experience_Level]=[Forms]![frmAdmin_Shifts]![cbo01_Ex], [tblRecords_Shift_Details]![Shift_Status]=[Forms]![frmAdmin_Shifts]![cbo01_Sh] , [tblRecords_Shift_Details]![Month_Number]=[Forms]![frmAdmin_Shifts]![cboMonth], [tblRecords_Shift_Details]![Date_of_Month]=DateSerial(2004, val([Forms]![frmAdmin_Shifts]![cboMonth]),1)  
WHERE recID = [tblRecords_Shift_Details]![RecordID];
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12114095
Danny, do this:

   sSQL = "INSERT INTO tblRecords_Shift_Details ( Month_Number, Advisor, Shift_Status, Experience_Level, Date_of_Month ) SELECT [Forms]![frmAdmin_Shifts]![cboMonth], [Forms]![frmAdmin_Shifts]![cboAdvisor], [Forms]![frmAdmin_Shifts]![cbo01_Sh], [Forms]![frmAdmin_Shifts]![cbo01_Ex], #" & Format(DateSerial(2004,Val([Forms]![frmAdmin_Shifts]![cboMonth]),1),"mm/dd/yyyy") & "#"

You may get an error that states "Error in FROM clause" or something similar though. If you do, let me know.
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12114158
It looks like it should work - but it's prompting for "recID", rather than taking it from the result of the DLOOKUP statement.

Any ideas how to pass the variable across - or do I have to add it to the update statement?

ie:

sSQL = "UPDATE tblRecords_Shift_Details SET [tblRecords_Shift_Details]![Advisor]=[Forms]![frmAdmin_Shifts]![cboAdvisor], [tblRecords_Shift_Details]![Experience_Level]=[Forms]![frmAdmin_Shifts]![cbo01_Ex], [tblRecords_Shift_Details]![Shift_Status]=[Forms]![frmAdmin_Shifts]![cbo01_Sh] , [tblRecords_Shift_Details]![Month_Number]=[Forms]![frmAdmin_Shifts]![cboMonth], [tblRecords_Shift_Details]![Date_of_Month]=DateSerial(2004, val([Forms]![frmAdmin_Shifts]![cboMonth]),1) WHERE DLookup("[recordID]", "tblRecords_Shift_Details", "[Advisor] =" & Chr(34) & Me.cboAdvisor & Chr(34) & " And [Date_of_month] =  #" & Format(DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1), "mm/dd/yyyy") & "#")= [tblRecords_Shift_Details]![RecordID];"
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12114176
Shane- the original INSERT statement is fine.
It's the UPDATE query that I'm trying to fix.....
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12114178
Hold on - what's this part all about:
= [tblRecords_Shift_Details]![RecordID]

?
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12114196
Try this:

sSQL = "UPDATE tblRecords_Shift_Details SET [tblRecords_Shift_Details]![Advisor]=[Forms]![frmAdmin_Shifts]![cboAdvisor], [tblRecords_Shift_Details]![Experience_Level]=[Forms]![frmAdmin_Shifts]![cbo01_Ex], [tblRecords_Shift_Details]![Shift_Status]=[Forms]![frmAdmin_Shifts]![cbo01_Sh] , [tblRecords_Shift_Details]![Month_Number]=[Forms]![frmAdmin_Shifts]![cboMonth], [tblRecords_Shift_Details]![Date_of_Month]=DateSerial(2004, val([Forms]![frmAdmin_Shifts]![cboMonth]),1) WHERE [tblRecords_Shift_Details]![RecordID] = DLookup("[recordID]", "tblRecords_Shift_Details", "[Advisor] =" & Chr(34) & Me.cboAdvisor & Chr(34) & " And [Date_of_month] =  #" & Format(DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1), "mm/dd/yyyy") & "#");"
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12114252
> Hold on - what's this part all about:     = [tblRecords_Shift_Details]![RecordID]

In the original DLOOKUP statement, if the record exists, I return the RecordID from the table.
If a recordID is returned, I want to update that record - hence the WHERE recID = table recID
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12114270
Sam - usual error problems with " in a string...
0
 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 1200 total points
ID: 12114302
Try it with single quotes in the format section of the DLookup:

sSQL = "UPDATE tblRecords_Shift_Details SET [tblRecords_Shift_Details]![Advisor]=[Forms]![frmAdmin_Shifts]![cboAdvisor], [tblRecords_Shift_Details]![Experience_Level]=[Forms]![frmAdmin_Shifts]![cbo01_Ex], [tblRecords_Shift_Details]![Shift_Status]=[Forms]![frmAdmin_Shifts]![cbo01_Sh] , [tblRecords_Shift_Details]![Month_Number]=[Forms]![frmAdmin_Shifts]![cboMonth], [tblRecords_Shift_Details]![Date_of_Month]=DateSerial(2004, val([Forms]![frmAdmin_Shifts]![cboMonth]),1) WHERE [tblRecords_Shift_Details]![RecordID] = DLookup("[recordID]", "tblRecords_Shift_Details", "[Advisor] =" & Chr(34) & Me.cboAdvisor & Chr(34) & " And [Date_of_month] =  #" & Format(DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1), 'mm/dd/yyyy') & "#");"
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12114369
No lad...

sSQL = "UPDATE tblRecords_Shift_Details "
sSQL = sSQL & "SET [tblRecords_Shift_Details]![Advisor]=[Forms]![frmAdmin_Shifts]![cboAdvisor], "
sSQL = sSQL & "[tblRecords_Shift_Details]![Experience_Level]=[Forms]![frmAdmin_Shifts]![cbo01_Ex], "
sSQL = sSQL & "[tblRecords_Shift_Details]![Shift_Status]=[Forms]![frmAdmin_Shifts]![cbo01_Sh], "
sSQL = sSQL & "[tblRecords_Shift_Details]![Month_Number]=[Forms]![frmAdmin_Shifts]![cboMonth], "
sSQL = sSQL & "[tblRecords_Shift_Details]![Date_of_Month]=DateSerial(2004, val([Forms]![frmAdmin_Shifts]![cboMonth]),1) "
        '*******All OK to this point
sSQL = sSQL & "WHERE [tblRecords_Shift_Details]![RecordID] = DLookup("[recordID]", "tblRecords_Shift_Details", "[Advisor] =" & Chr(34) & Me.cboAdvisor & Chr(34) & " And [Date_of_month] =  #" & Format(DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1), 'mm/dd/yyyy') & "#");"
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12114379
Right, let's take a step back for a moment and look at building strings. When you build a string, you do it like this:
strSQL="Some text"

If you want to embed a " in the string, you have several options - use a "" or a Chr(34). So you can do this to make the string equal to: He said "Hi"
strSQL="He said ""Hi"""
strSQL="He said " & Chr(34) & "Hi" & Chr(34)
but this won't work:
strSQL="He said "Hi""
The string "breaks" at the first " character, because that is where VBA thinks that you've ended the string.

Now, when you are building your SQL string above, you have a DLookup statement in it that contains a " character. This causes the string to "break" at the parameter start. First, let's simplify your query. Since tblRecords_Shift_Details is the only table involved, you don't need the table name qualifier - you can just use the field names, so:

sSQL=  "UPDATE tblRecords_Shift_Details SET [Advisor]=[Forms]![frmAdmin_Shifts]![cboAdvisor], [Experience_Level]=[Forms]![frmAdmin_Shifts]![cbo01_Ex], [Shift_Status]=[Forms]![frmAdmin_Shifts]![cbo01_Sh] , [Month_Number]=[Forms]![frmAdmin_Shifts]![cboMonth], [Date_of_Month]=DateSerial(2004, val([Forms]![frmAdmin_Shifts]![cboMonth]),1) WHERE DLookup('[recordID]', 'tblRecords_Shift_Details', '[Advisor] ='" & Me.cboAdvisor & "' And [Date_of_month]=DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1) And [tblRecords_Shift_Details]![RecordID]=" & recID

We simply replace the " in the Dlookup parameters with a ', making it part of the string. You could achieve the same effect by replacing them with "":
sSQL=  "UPDATE tblRecords_Shift_Details SET [Advisor]=[Forms]![frmAdmin_Shifts]![cboAdvisor], [Experience_Level]=[Forms]![frmAdmin_Shifts]![cbo01_Ex], [Shift_Status]=[Forms]![frmAdmin_Shifts]![cbo01_Sh] , [Month_Number]=[Forms]![frmAdmin_Shifts]![cboMonth], [Date_of_Month]=DateSerial(2004, val([Forms]![frmAdmin_Shifts]![cboMonth]),1) WHERE DLookup(""[recordID]"", ""tblRecords_Shift_Details"", ""[Advisor] =""" & Me.cboAdvisor & """ And [Date_of_month]=DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1) And [tblRecords_Shift_Details]![RecordID]=" & recID
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12114506
Sorry Shane:

sSQL = sSQL & "WHERE DLookup(""[recordID]"", ""tblRecords_Shift_Details"", ""[Advisor] =""" & Me.cboAdvisor & """ And [Date_of_month]=DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1) And [tblRecords_Shift_Details]![RecordID]=" & recID

Still gives an error for Syntax (missing operator)
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12114568
Sorry guys, I'm not feeling well, so I'll sign off for now...

Good luck!

Sam
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12114588
Danny,
If you still are having trouble with this query in an hour, post the database and I'll fiddle more with it (I usually have more success working with a copy of the DB).

God bless!

Sam
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12114664
Danny,

Try running the code, and post what sSQL evaluates to.
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12114675
I'm gonna have a sleep on it guys - will be back in about 12 hrs or so...

:-)

Thanks again

(Get well soon Sam)
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12115422
an approach I have used to help me see things clearly is to build each value as a seperate variable. This way I know what the data type is for each variable by its prefix and thus know if/how to wrap it. I also like to split the code up so I can read it without trying to parse it all in my head. I know that creates extra overhead for Access to conmcatenate back together but the performance hit is negligable as compared to the headaches I cause myself :-)

you already got the recordid in the original DLookup so why make the datbase look it up all over again?

strAdv = [Forms]![frmAdmin_Shifts]![cboAdvisor]
lngExp=[Forms]![frmAdmin_Shifts]![cbo01_Ex]

sSQL=  "UPDATE tblRecords_Shift_Details " & _
            "SET [Advisor]='" & strAdv & "', " _
                   "[Experience_Level]=" & lngExp

...

Steve

0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 800 total points
ID: 12115475
another good one to store is the date your are building ...

dtmMydate = Format(DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1), 'mm/dd/yyyy')

Because this date value is used as criteria the query engine is (I think) going to have to re-calculate it for every single row in a full table scan but if you build the value directly it never has to calculate it and it could optimize on an index of that field (if it exists).

Steve
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12120287
I'm back guys - cheers for having a look Steve
The dtmMyDate option is one that I normally use - but after I've understood what's going on in the query (which normally takes longer than to build the damn thing!)

In this case, Shanes Format(DateSerial.. etc works no problem.

My problem seems to be the WHERE clause and specifically a supposed "Missing Operator", which is causing the syntax error.

This is what I have at the moment:

sSQL = "UPDATE tblRecords_Shift_Details "
sSQL = sSQL & "SET [tblRecords_Shift_Details]![Advisor]=[Forms]![frmAdmin_Shifts]![cboAdvisor], "
sSQL = sSQL & "[tblRecords_Shift_Details]![Experience_Level]=[Forms]![frmAdmin_Shifts]![cbo01_Ex], "
sSQL = sSQL & "[tblRecords_Shift_Details]![Shift_Status]=[Forms]![frmAdmin_Shifts]![cbo01_Sh], "
sSQL = sSQL & "[tblRecords_Shift_Details]![Month_Number]=[Forms]![frmAdmin_Shifts]![cboMonth], "
sSQL = sSQL & "[tblRecords_Shift_Details]![Date_of_Month]=DateSerial(2004, val([Forms]![frmAdmin_Shifts]![cboMonth]),1) "
sSQL = sSQL & "WHERE DLookup(""[recordID]"", ""tblRecords_Shift_Details"", ""[Advisor] =""" & Me.cboAdvisor & """ And [Date_of_month]=DateSerial(2004, Val([Forms]![frmAdmin_Shifts]![cboMonth]), 1) And [tblRecords_Shift_Details]![RecordID]=" & recID

The error message is
"Syntax error (Missing Operator) in query expression 'DLOOKUP blah blah blah '."
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12120345
As Steve says, the DLookup is probably unnecessary, since you already have the recID you are interested in (btw, the error is because there's a missing ) in the string, but I don't know where it should go). Change the last line to:
sSQL = sSQL & "WHERE [tblRecords_Shift_Details]![RecordID]=" & recID
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12120375
Spot on Shane....

You'll be pleased to know that that is the last DLOOKUP/DCOUNT/DPILEOFCACK command that I need for this db...

I will learn how to use it properly - always seems to be a problem with the " "

Thanks for your pointers (yet again)

I'm going to share the points for this question with Sam and Steve
I'll post a seperate link with some for you.

Thanks again Shane.
0
 
LVL 26

Author Comment

by:dannywareham
ID: 12120389
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12120620
ThanX Danny!
God bless!

Sam
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question