Not Sure what this systax error is all about (1 Viewer)

anishkgt · Aug 6, 2018

I get a syntax error for this

Code:

DLookUp("[LockNum]","tblImportLock","[Open]=1")

LockNum is of DataType Number

I used macro to code the form. It just checks a filed value for 1 or 0. It had been working but just today an error poped-up.

isladogs · Aug 6, 2018

You haven't said what error occurs.
Without that, all we can do is guess the problem.

My guess is you get invalid use of null error.
If so, wrap your expression in the Nz function
Nz(DLookup(.....,"')

Also, what is the datatype for the Open field?
If it's yes/no (Boolean) use false instead of 0.

Finally, the [] brackets aren't needed in your case as you have no spaces in field names

anishkgt · Aug 6, 2018

the error just says "Syntax Error"

anishkgt · Aug 6, 2018

The open field is number so that i could write it 0 or 1. If its Yes or NO it would be a check box so let it as number.

anishkgt · Aug 6, 2018

Here is the complete macro

Minty · Aug 6, 2018

You aren't comparing the result of the dlookup to anything.
It should be

Code:

If DLookUp("LockNum","tblImportLock","[Open]=1")[COLOR="Red"] = 1 [/COLOR]Then

CJ_London · Aug 6, 2018

general point if you are not returning a value for a field to use (as you are here - you just want to know if a record exists), use DCount

if DCount("*","tblImportLock","[Open]=1")<>0 then

Not sure what your logic is supposed to be but if the only options are 0 or 1 then your ElseIf only needs to be an Else - otherwise you are interrogating the data twice

Finally, recommend you learn VBA and don't use macros - they are limited in functionality and as you have discovered, the error messages can be vague

anishkgt · Aug 7, 2018

I get the same error over and over again " Syntax Error"

Minty · Aug 7, 2018

So what is your code now - we can't guess I'm afraid.
Please show us what you have now.

anishkgt · Jan 10, 2019

Attached here is the complete code to uploading data from a text file into a database

CJ_London · Jan 10, 2019

so you expect us to scroll through hundreds of lines of text with no idea where to look for a syntax error and no documentation of what the code is doing? I see no use of dlookup or dcount in your code.

Syntax errors occur when you are writing the code so you should know exactly on what line the error occurs

All I can say is it seems a very complicated way of importing data. I would have thought it could be accomplished with a few lines of sql.

Perhaps explain what you are trying to do, provide some example data and the outcome required from that data.

anishkgt · Jan 10, 2019

Sorry ! i thought it would be obvious as it was mentioned in the previous posts.
the line

Code:

If DCount("FileID", "qryMIR", "[MIRSeq]=" & MIRSeq & " and [TicketNumber]=""" & tkt & """") Then 'validate MIR Sequence number and Ticketnumber to avoid Duplication

throws an error.

A sample file to read is attached which is read and imported into access database. The line with the systax error checks a query for repeating MIRSeq number and Ticket number.

The thread discussing about the code is here

CJ_London · Jan 10, 2019

this has already been answered - see post #6. Same applies, you need

if dcount(…….)=0 then

or perhaps <>0, depends on what you want the code to do

isladogs · Jan 10, 2019

The thread you linked has 320 posts. Also far too many to check although you were involved at the end.
Not sure what the new text file is meant to show without spending time reading that thread.
Please help us to help you.

In your DCount, as well as adding >0 or similar, is MIRSeq a number field and TicketNumber a text field (despite it's name)? If not you will get errors

anishkgt · Jan 10, 2019

MIRSeq is a number and TicketNumber is a short text

anishkgt · Jan 10, 2019

So i just copied the same access file on my work desktop and there it seems to work without a problem.....No syntax error.

Not Sure what this systax error is all about (1 Viewer)

anishkgt

Registered User.

isladogs

MVP / VIP

anishkgt

Registered User.

anishkgt

Registered User.

anishkgt

Registered User.

Attachments

Minty

AWF VIP

CJ_London

Super Moderator

anishkgt

Registered User.

Minty

AWF VIP

anishkgt

Registered User.

Attachments

CJ_London

Super Moderator

anishkgt

Registered User.

Attachments

CJ_London

Super Moderator

isladogs

MVP / VIP

anishkgt

Registered User.

anishkgt

Registered User.

Similar threads

Users who are viewing this thread