Need Help with run time error 3075

[vkumar]

I am getting run time error 3075 syntax error missing operator in query expression '[Job Number] = '

Here is my code:

Private Sub Job_Number_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[Job Number]", "[Job]", "[Job Number]= " & Me.[Job Number] & " ")) Then
MsgBox "Job Number doesn't exist Enter a job number that already exist."
Cancel = True
End If
End Sub

Please help! Thank you

[pbaldy]

Given the error, it appears that the form control is Null. Should it have an underscore? Also, why not use a combo box so the user can't enter a bad number?

[amrut]

I think the space at the end shown in red may be throwing an error. Remove that part and try.
If IsNull(DLookup("[Job Number]", "[Job]", "[Job Number]= " & Me.[Job Number] & " ")) Then

[vkumar]

Thank you for your reply.
When I enter correct Job number it is working fine, only If I delete the job number and clear the form it gives that error. There are thousands of job number, the customer didn't want the combo box.

Given the error, it appears that the form control is Null. Should it have an underscore? Also, why not use a combo box so the user can't enter a bad number?

[vkumar]

Thank you for your reply. I tried after removing the space, it didn't work.

I think the space at the end shown in red may be throwing an error. Remove that part and try.
If IsNull(DLookup("[Job Number]", "[Job]", "[Job Number]= " & Me.[Job Number] & " ")) Then

[Missinglinq]

Two things:

At the Table-level, is [Job Number] defined as a Number Datatype? I ask the syntax you're using is only correct for a Number, not for Text. A Field made up entirely of digits can, of course, be defined as Text.

When doing this type of a check, the DCount() is usually used, rather than DLookup():

If DCount("[Job Number]", "[Job]", "[Job Number]= " & Me.[Job Number]) > 0 Then

Linq ;0)>

[pbaldy]

Thank you for your reply.
When I enter correct Job number it is working fine, only If I delete the job number and clear the form it gives that error. There are thousands of job number, the customer didn't want the combo box.

If it's only failing when you delete a job number, test the job number before running the DLookup to make sure it has a value.

[vkumar]

Job number is a number field. Thank you for your reply.

Two things:

At the Table-level, is [Job Number] defined as a Number Datatype? I ask the syntax you're using is only correct for a Number, not for Text. A Field made up entirely of digits can, of course, be defined as Text.

When doing this type of a check, the DCount() is usually used, rather than DLookup():

If DCount("[Job Number]", "[Job]", "[Job Number]= " & Me.[Job Number]) > 0 Then

Linq ;0)>

[vkumar]

If I enter a correct job number no problem. But if I don't have the correct job number, I can't clear the form or I can't close the form until I enter a correct job number. It gives the run time error. Once if enter the correct job number, I can clear or close the form. When the user makes a mistake and don't have the correct job number then the user is stuck there and can't close the form and can't come out. It is not a good design of a DB. I really need to fix this glitch.

If it's only failing when you delete a job number, test the job number before running the DLookup to make sure it has a value.

[pbaldy]

Like I said, first test to make sure it has something in it:

If Len(Me.[Job Number] & vbNullString) > 0 Then

[vkumar]

Sorry for being so dumb. I am not able to incorportae two if statements (I tried And it didn't work). How to do it with the code I have. Job number is a number field. Thank you.

Like I said, first test to make sure it has something in it:

If Len(Me.[Job Number] & vbNullString) > 0 Then

[pbaldy]

Put that line first and another

End If

last.

[vkumar]

This is what I have now. It still gives me the run time error. Unless the user enters the correct Job number, the user can't clear the form or close the form.

Private Sub Job_Number_BeforeUpdate(Cancel As Integer)
If Len(Me.[Job Number] & vbNullString) > 0 Then
If IsNull(DLookup("[Job Number]", "[Job]", "[Job Number]= " & Me.[Job Number] & " ")) Then
MsgBox "Job Number doesn't exist Enter a job number that already exist."
Cancel = True
End If
End If

Put that line first and another

End If

last.

[pbaldy]

It should only be running the DLookup if there's a value in the job number. Can you post the db here?

[vkumar]

Myfile is too big to attach it here, I have stored it in free online storage place and you can use the following link to download (Judy IMS.accdb) it.


http://www.sendspace.com/file/nlbsgj

Thank you again for helping me.

P.S. In the Enter Payment form if you don't enter the correct Job Number, you can't clear the form or close it. If enter the correct Job number you can clear the form or close it after clearing the form.

It should only be running the DLookup if there's a value in the job number. Can you post the db here?