Syntax error using dlookup

Hi All,

I have the following piece of code 

ChkProv = DLookup("[Province_Cde]", "Provinces", "[Province_Cde]= ' " & 
Left(Nat_Reg, 2))

which I want to use as part of validating that the 1st 2 left chrs of 
Nat_Reg correspond with a record in field Province_Cde

However it gives me the following error when executed

Syntax error in string in query expression '[Province_Cde]='GU'

GU is valid in the record I was trying to add as a test & it exists in the 
table Provinces.Province_Cde

Any suggestions on what to try to correct this?

Hugh
0
Utf
11/13/2009 8:45:02 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

4 Replies
1274 Views

Similar Articles

[PageSpeed] 24

Missing a close quote mark and have a leading space in the third argument 
before getting the two characters from Nat_Reg.

ChkProv = DLookup("Province_Cde", "Provinces", "Province_Cde= '" & 
Left(Nat_Reg, 2) & "'")



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hugh self taught wrote:
> Hi All,
> 
> I have the following piece of code 
> 
> ChkProv = DLookup("[Province_Cde]", "Provinces", "[Province_Cde]= ' " & 
> Left(Nat_Reg, 2))
> 
> which I want to use as part of validating that the 1st 2 left chrs of 
> Nat_Reg correspond with a record in field Province_Cde
> 
> However it gives me the following error when executed
> 
> Syntax error in string in query expression '[Province_Cde]='GU'
> 
> GU is valid in the record I was trying to add as a test & it exists in the 
> table Provinces.Province_Cde
> 
> Any suggestions on what to try to correct this?
> 
> Hugh
0
John
11/13/2009 9:02:20 PM
Hugh self taught wrote:
>ChkProv = DLookup("[Province_Cde]", "Provinces", "[Province_Cde]= ' " & 
>Left(Nat_Reg, 2))
>
>which I want to use as part of validating that the 1st 2 left chrs of 
>Nat_Reg correspond with a record in field Province_Cde
>
>However it gives me the following error when executed
>
>Syntax error in string in query expression '[Province_Cde]='GU'
>
>GU is valid in the record I was trying to add as a test & it exists in the 
>table Provinces.Province_Cde
>

You forgot the closing '

ChkProv = DLookup("[Province_Cde]", "Provinces",
"[Province_Cde]= '" & Left(Nat_Reg, 2) & "' ")

-- 
Marsh
MVP [MS Access]
0
Marshall
11/13/2009 9:12:14 PM
You forgot your closing ', try:

ChkProv = DLookup("[Province_Cde]", "Provinces", "[Province_Cde]= ' " & 
Left(Nat_Reg, 2) & "'")

-- 
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Hugh self taught" wrote:

> Hi All,
> 
> I have the following piece of code 
> 
> ChkProv = DLookup("[Province_Cde]", "Provinces", "[Province_Cde]= ' " & 
> Left(Nat_Reg, 2))
> 
> which I want to use as part of validating that the 1st 2 left chrs of 
> Nat_Reg correspond with a record in field Province_Cde
> 
> However it gives me the following error when executed
> 
> Syntax error in string in query expression '[Province_Cde]='GU'
> 
> GU is valid in the record I was trying to add as a test & it exists in the 
> table Provinces.Province_Cde
> 
> Any suggestions on what to try to correct this?
> 
> Hugh
0
Utf
11/13/2009 9:29:01 PM
Hi John, Marshall & Daniel

Many thanks for the help. Although my knowledge is growing I'm not 
proficient at find my intricate errors yet. Works like a charm now

Hugh

"John Spencer" wrote:

> Missing a close quote mark and have a leading space in the third argument 
> before getting the two characters from Nat_Reg.
> 
> ChkProv = DLookup("Province_Cde", "Provinces", "Province_Cde= '" & 
> Left(Nat_Reg, 2) & "'")
> 
> 
> 
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Hugh self taught wrote:
> > Hi All,
> > 
> > I have the following piece of code 
> > 
> > ChkProv = DLookup("[Province_Cde]", "Provinces", "[Province_Cde]= ' " & 
> > Left(Nat_Reg, 2))
> > 
> > which I want to use as part of validating that the 1st 2 left chrs of 
> > Nat_Reg correspond with a record in field Province_Cde
> > 
> > However it gives me the following error when executed
> > 
> > Syntax error in string in query expression '[Province_Cde]='GU'
> > 
> > GU is valid in the record I was trying to add as a test & it exists in the 
> > table Provinces.Province_Cde
> > 
> > Any suggestions on what to try to correct this?
> > 
> > Hugh
> .
> 
0
Utf
11/14/2009 6:46:01 PM
Reply:

Similar Artilces:

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

crm 3.0 error 03-01-06
Hello, I'me getting this error while installing crm3.0 for SBS: "error writing to file microsoft.mshtml.dll verify that you have access to that directory" That file is in the C:\Program Files\Microsoft.NET\Primary Interop Assemblies directory. I (and 'everyone') has full access to that dir. What can I do about this?? kind regards, Thomas ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Error in Outlook Today
Whenever I go to Outlook Today, I get a runtime error, line: 297 Error: Class Not Registered. Then I get the error two more times when I click 'Customize Outlook Today...' and the list 'Show Outlook Today In This Style' is empty, and the box under it has a broken image icon. What could be the cause of this? Sorry...forgot to say...I'm using Outlook 2003 Student and Teacher Edition on Windows XP. >-----Original Message----- >Whenever I go to Outlook Today, I get a runtime error, >line: 297 Error: Class Not Registered. Then I get the >error two more time...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Re: 'Uknown Error 0x800CCC97'
I just heard back from the folks with whom I filed this bug. They say the bug is fixed in cppop 5.4 - request that your ISP upgrade to that. -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Jeff Stephenson [MSFT]" <stephenson@online.microsoft.com> wrote in message news:... > See the attached reply to another similar question. Your ISP's POP3 server > has a bug, and they should get a fixed version of the server. > > -- > Jeff Stephenson > Outlook Development > This posting...

stop error defeating me
Hi, XP Pro PC. When I start the computer I can start in safe mode but when I try to start in normal mode it loads to the log on screen. I type the username and password in then it starts to load but stops after a few moments with a blue screen. The error is Stop: c000021a (fatal system error) The windows subsystem system process terminated unexpectedly with a status of 0xc0000005 (0x7c9106c3 0x0055f36c). Begininning dump of physical memory. I have uninstalled AVG, also taken out the graphics card and uninstalled all the drivers for it. I have also changed the RAM. I have also d...

Error in database....
A user posted a batch in payables management. After posting, there was an error encountered. It displays that the table updating was interrupted, use batch recovery to continue the posting. But when I used the batch recovery, it was not successful to continue the update process. When I click the "More Details" button it displays, A save operation on table 'PM_Transaction_WORK' caused a sharing error. How can I resolve this issue? Thanks, John John, it is a db sharing violation. Have all users logout DELETE tempdb..DEX_LOCK DELETE tempd..DEX_SESSION DELETE dynami...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

error 553
The following error occurs when sending email from my business domain. It does not occur when sending through my roadrunner account. The following recipient(s) could not be reached: on 10/22/2003 2:05 PM 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1) What does this mean and how can it be fixed? ...

80070005 error #2
I am getting this error when trying to view public folder property from system manager. My issue is same as what you can find out from http://forums.msexchange.org/ultimatebb.cgi? ubb=get_topic;f=19;t=000114 Anyone has a clue? ...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...

Anyone use Promys CRM software integrated with GP Dynamics?
Our company is considering using Promys as a CRM to create quotes and sales orders for the sales team. I am concerned about the integration with GP and what the pitfalls may be. Is anyone here currently using Promys with GP Dynamics? ...

Loading Text File to TextBox using LoadFromFile
Hi All, I'm creating a form that allows the user to pick a txt file (dialog) and then display the path and contents on the form. The code has been cobbled together as I found the pieces that worked, so bear with. I got the file picker working and displaying the file name on the form, but the file contents won't display. I had a feeling the problem had to do with importing a namespace (see the error in the code when I tried "Imports System.IO") or with a missing reference. Using Access 2003. References: VB for Apps, MS Access 11 Obj Lib, OLE Auto, MS V...

Can't use openURL or access internet on some locations
I have previously raised a question where openUrl threw an exception in a specific office location. However the problem I have now is that the application just freeze when I call openURL. It seems as it is waiting for something. This only happens at one company so far the company does have a proxy but so do I at work and I have no problems. I have also tried the Microsoft TEAR sample and it behaves the exact same way, it says "Opening internet...Connection made" and then it just stops. If I run it on my computer it gets the webpage and everything finishes ok. I have tried to set t...

CRM Error
Hello When a user replies to an CRM email, clicks the "reply" button or the "reply all" button, clicks in the body of the email message and clicks "insert template", this error appears. This does not happen every time, and happens to various users. Does anyone know why we would get this error? ...

smtp authentification errors
Hello, I just installed Exchange 2003. It all works fine. I=B4m now=20 retrieving the emails via pop-con and pop3. However I=20 cannot send any authenticicated emails, because the=20 exchange server sends a wrong authentification account to=20 the mail provider, despite the fact, that the right one to=20 be used is enterd. The mail provider tells me that he recives a smtp call,=20 but it=B4s aborted by his server. We do not have a static IP but a dynamic IP, that changes=20 every 24 hours. If a messages is sent to an existing hotmail account the=20 following error message is displayed in the ...

#error in the calculated field
Hello, I locked the data entered for some users, because their role is just to input the date of invoice for approval by Prj.Manager. My qeustion is, is it the reason we see the "# error" in the VAT checking field??. I tried to ck formula in the qrid query, nothing wrong Thanks for your explanation -- H. Frank Situmorang On Tue, 15 Jan 2008 23:33:01 -0800, Frank Situmorang <hfsitumo2001@yahoo.com> wrote: >Hello, > >I locked the data entered for some users, because their role is just to >input the date of invoice for approval by Prj.Manager. > >My q...

Error when changing average perpetual
I Have the following error in Microsoft Dynamics GP on the screen of average perpetual "Message #10577 Missing" somebody knows about this??? Thankļæ½s. Gabriela Martinez. ...