syntax error(missing operation) in a vba query

mana

Registered User.
Local time
Today, 13:59
Joined
Nov 4, 2014
Messages
265
[Hello i Hve the following code and i have run time errro that
there is a syntax error(missing operation) in query expression.
can you help me please?


Me.RecordSource = "SELECT * FROM qry_Logistikreview_Zeitfehlerein WHERE qry_Logistikreview_Zeitfehlerein.Zeit Fehler Ein BETWEEN #" & Replace(Format(Me.txt_StartDate.Value, "mm/dd/YYYY"), ".", "/") & "# " & _
"AND #" & Replace(Format(DateAdd("d", 1, Me.txt_EndDate.Value), "mm/dd/YYYY"), ".", "/") & "#"



DoCmd.Requery
 
First of all - Tell us what you are trying to achieve.

This code could relate to anything.
 
Not sure why the use of Replace function, also you need to enclose the Field Name that has special characters (that includes spaces) inside square brackets. Try giving this a shot.
Code:
Me.RecordSource = "SELECT * FROM qry_Logistikreview_Zeitfehlerein " & _
                  "WHERE qry_Logistikreview_Zeitfehlerein.[COLOR=Red][B][Zeit Fehler Ein] [/B][/COLOR]BETWEEN " & Format(Me.txt_StartDate, "\#mm\/dd\/yyyy\#") & _
                  " AND " & Format(DateAdd("d", 1, Me.txt_EndDate), "\#mm\/dd\/yyyy\#")
 
Hello thank you very much now it works without error but i have anothe rproblem
when i choose the date between the fields there are some data but it doesn't show anything and i don't know why. cna oyu help me please?

zeit fehler ein is like the below example:
2014-12-04

can you help me please?
 
It doesnt show anything, what does that mean?
Does the date exist? Does it have values?
Have you checked your data in the query/table, to see if the problem is there already?
 
Hello

yes the data exist and will be shown in the query but i can't see in the program.
i checked it in the query and it will be shown
 
Do you have controls on the form to actually display the data?
 
Hello

what do you mean with control

i Have just some text boxes and also a button that does the selection for me
 
For the columns in your query do you have a place / control / textbox to display the information contained in the query
 
hello i have the text box in my form to show the result
 
Not sure why the use of Replace function,

In the Format function, the forward slash is not literal but rather a placeholder for the regional date separator. In the case of the original poster, I assume their regional date separator is a dot. Hence the format "mm/dd/yyyy" would return "mm.dd.yyyy".

This is why we use "mm\/dd\/yyyy" as the format string. The backslash means the subsequent character is literal rather than being interpreted as the placeholder. This makes it more tolerant of regional settings.

My preference is to include the hashes in the string as Paul showed, "\#mm\/dd\/yyyy\#".
 
Hello

yes the data exist and will be shown in the query but i can't see in the program.
i checked it in the query and it will be shown

In the standard Bound form or report technique, there are two essential steps to getting data from a query into textboxes.

1. The RecordSource property of the form or report needs to be the name of the query.

2. The ControlSource property of the textboxes need to be the names of the corresponding fields in the RecordSource. (The Controlsource is what displays in the textbox in Design View.)
 
hello

thank you
i know these but i have still the problem
when i open the form it has all of the values in zeit fehler ein field
but when i want to filter it , it doesn't show anything
 
hello

you know before i hade this date as 17.10.2014 that worked with this

Me.RecordSource = "SELECT * FROM qry_Logistikreview_Zeitfehlerein WHERE qry_Logistikreview_Zeitfehlerein.Zeit Fehler Ein BETWEEN #" & Replace(Format(Me.txt_StartDate.Value, "mm/dd/YYYY"), ".", "/") & "# " & _
"AND #" & Replace(Format(DateAdd("d", 1, Me.txt_EndDate.Value), "mm/dd/YYYY"), ".", "/") & "#"

code but now i have this field with the value 2014-12-04
do you know what i should write for this field
i tried different things but they didn't work
can you help please?
 
before i hade this date as 17.10.2014 that worked with this
If it worked with 17.10.2014 then why don't you use that? Presumably that is your regional date format.

Me.RecordSource = "SELECT * FROM qry_Logistikreview_Zeitfehlerein WHERE qry_Logistikreview_Zeitfehlerein.Zeit Fehler Ein BETWEEN #" & Replace(Format(Me.txt_StartDate.Value, "mm/dd/YYYY"), ".", "/") & "# " & _
"AND #" & Replace(Format(DateAdd("d", 1, Me.txt_EndDate.Value), "mm/dd/YYYY"), ".", "/") & "#"

I don't see how it could have worked with that since there are spaces in the field name.

Your expression (if you include the brackets around the field name) should give the same result as the solution in post 3, provided that your regional date format is dd.mm.yyyy

Are you sure the [Zeit Fehler Ein] field is DateTime datatype?
 
hello,

my date is like 2014-02-13
I have - instead of . (2014-03-14 instead of 2014.03.14)
can you help me please?
 

Users who are viewing this thread

Back
Top Bottom