Syntax error (missing operator) in query expression

Question

i have a simple form and i want to filter date  using a text box and one button

i use this code

Private Sub cmdtanggal_Click()
Me.RecordSource = "SELECT Manajer Informasi.[Tanggal_SPMKI], Manajer Informasi.[Nama_manajer_Informasi], Manajer Informasi.[NIP], Manajer Informasi.[Satuan_Kerja], Manajer Informasi.[Jabatan], Manajer Informasi.[Cakupan_Informasi]  FROM Manajer Informasi WHERE (((Manajer_Informasi.[Tanggal_SPMKI])=(forms)!(formManajerInformasi)!(txttanggalSPMKI)))"
Me.Requery
End Sub

and when i run i have Syntax error (missing operator) in query expression 'Manajer Informasi.[Tanggal_SPMKI]'

could you help me for this problem

regards

riantoaji

Answer 1

Since your table name "Manajer Informasi" contains a space, you need to surround it with square brackets.  In addition to that, your reference to the control on the form is not constructed properly.  Try this:

Private Sub cmdtanggal_Click()

    Me.RecordSource = "SELECT [Manajer Informasi].[Tanggal_SPMKI], [Manajer Informasi].[Nama_manajer_Informasi], [Manajer Informasi].[NIP], [Manajer Informasi].[Satuan_Kerja], [Manajer Informasi].[Jabatan], [Manajer Informasi].[Cakupan_Informasi] FROM [Manajer Informasi] WHERE [Manajer Informasi].[Tanggal_SPMKI]=[Forms]![formManajerInformasi]![txttanggalSPMKI]"

 End Sub

In the above, I've also removed your statement "Me.Requery", because it isn't necessary.  Any time you set the form's RecordSource property, the form is automatically requeried.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

The parentheses are superfluous. Should be:

WHERE Manajer_Informasi.[Tanggal_SPMKI]=forms!formManajerInformasi!txttanggalSPMKI"

Just out of curiosity: what is your language? I'm Dutch originally and if I had to guess it would be Indonesian, or maybe Afrikaander.

---

-Tom. Microsoft Access MVP

Answer 3

Hi, Since the name of your table includes a space, you'll need to enclose the name in square brackets. For example: [Table Name].[Field Name]

Answer 4

"manajer Informasi" is a query name that i want to make to be a form.

when 1 use your code it doesn't work

regards

Riantoaji

Answer 5

i'm indoneisan

and it doesn't work

regards

riantoaji

Answer 6

is it possible for [query].[field Name] because manajer informasi is a query

if the name field have a space can i use underscore (_) for change the space?

regards

riantoaji

Answer 7

Since your table name "Manajer Informasi" contains a space, you need to surround it with square brackets.  In addition to that, your reference to the control on the form is not constructed properly.  Try this:

Private Sub cmdtanggal_Click()

    Me.RecordSource = "SELECT [Manajer Informasi].[Tanggal_SPMKI], [Manajer Informasi].[Nama_manajer_Informasi], [Manajer Informasi].[NIP], [Manajer Informasi].[Satuan_Kerja], [Manajer Informasi].[Jabatan], [Manajer Informasi].[Cakupan_Informasi] FROM [Manajer Informasi] WHERE [Manajer Informasi].[Tanggal_SPMKI]=[Forms]![formManajerInformasi]![txttanggalSPMKI]"

 End Sub

In the above, I've also removed your statement "Me.Requery", because it isn't necessary.  Any time you set the form's RecordSource property, the form is automatically requeried.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

it doesnt work when i run and there is a message box for enter parameter value

Answer 8

Spaces are not appropriate in any object name, table name, query name, field name, change them all to not have spaces. They cause "notational difficulties" especially with beginners, as we can see here.

---

-Tom. Microsoft Access MVP

Answer 9

"manajer Informasi" is a query name that i want to make to be a form.

It doesn't matter whether it's the name of a query or of a table.  In the SQL statement, if it contains spaces or (most) punctuation characters, it has to be enclosed in square brackets in order to be properly interpreted.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 10

it doesnt work when i run and there is a message box for enter parameter value

We need more information than "it doesn't work".  If there's an error message, please state the message.  If now the only problem is that it prompts for a parameter value, we've made progress.  What value does it prompt for?  Is it the form/control reference, "[Forms]![formManajerInformasi]![txttanggalSPMKI]"?  If it is, then is that form open when you assign the SQL statement to the RecordSource?  If it is, then check the names of the form and the control;  you may have misspelled them.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 11

Hi riantoaji,

first of all try to create a query with proper names.

there are so many spaces in your query.

then you can try to refer textbox on the form.

then try to refer it like below.

Forms("frmCustomer")![Comments]

Reference:

Microsoft Access VBA Referring to Forms and Controls

so first you need to make sure that your query is running correctly and fetching the correct data.

to confirm that try to remove the where clause from your above query and directly run the query in Access.

if it returns the data then you can say your query is correct.

next again add the where clause and try to refer the textbox of form.

you need to make sure that you properly refer that textbox.

if it is asking for parameter for that where clause textbox value it means that it is not able to fetch the value from that textbox.

so you need to correct it.

you had mentioned that you are fetching the data from another query.

I can see that you are using field names in above query and uses many spaces that can generate error.

so for simplicity you can create another query and write all the field names in that query and run it to confirm it is ok.

then call that query and just use * to get all the fields from that query to avoid any error in field names. 

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 12

then you can try to refer textbox on the form.

then try to refer it like below.

=[ form name  ].Form![ control name  ] 

Reference:

references a control on another form

Deepak -

That is incorrect, and the referenced article is also incorrect in suggesting that this syntax will work except in the case where one is referring to a control on a subform from the main form.  For a query to refer to a control on a form, or for a control on one "main" form to refer to a control on another "main" form, the syntax is:

    Forms![form name]![control name]

This expression may or may not need a preliminary "=" sign, depending on where the reference is made. The square brackets are optional if the names involved don't include spaces or punctuation, and don't start with a numeric digit. 

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html