I have the following code under open report. It does exactly what I want. <br><br>SQL = "SELECT * from " & Forms!frmlogdate!logfile & " where " & Forms!frmlogdate!logfile & "!Initials = """ & Forms![frmlogdate]![INITIALS] & """"<br><br>I want to append the following code to the above query and I am not sure how to do it. I am just not sure where do you put quotes to end the query statement. It is probably very simple but I am having a lot of probles with query syntax. <br><br> and Forms!frmlogdate!logfile.logdate Is Not Null And Between [Forms]![frmlogdate]![BeginningDate] And [Forms]![frmlogdate]![EndingDate]<br><br><br>Any help will be appreciated.<br> <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
QUERY SYNTAX
- Thread starter villica
- Start date
Huh ?<br><br>SQL = "SELECT * from " & Forms!frmlogdate!logfile & " where " & Forms!frmlogdate!logfile & ".Initials = """ & Forms![frmlogdate].[INITIALS] & """ and #" & Forms!frmlogdate!logfile & ".logdate # Is Not Null And #" & [Forms]![frmlogdate].[BeginningDate] &" # >= #" & Forms!frmlogdate!logfile & ".logdate # And #" & [Forms]![frmlogdate].[EndingDate] & "# <= #" & Forms!frmlogdate!logfile & ".logdate #"<br><br><font color=red>You can greatly simplify your work by using the name of the logfile table</font>:<br><br>SQL = "SELECT * from tblLogFile where tblLogFile.Initials =""" & Forms![frmlogdate].[INITIALS] & """ and tblLogFile.logdate Is Not Null And #" & Forms![frmlogdate].[BeginningDate] & " # >= tblLogFile.logdate And #" & Forms![frmlogdate].[EndingDate]& "# <= tblLogFile.logdate"<br><br>Whew !<br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>
- Thread starter
- #3
Amiel thank you for your help, but I am getting a syntax error on the code you gave me.<br>SQL = "SELECT * from " & Forms!frmlogdate!logfile & " where " &<br> Forms!frmlogdate!logfile & ".Initials = """ & Forms![frmlogdate].[INITIALS] &<br> """ and #" & Forms!frmlogdate!logfile & ".logdate# Is Not Null And #" &<br> [Forms]![frmlogdate].[BeginningDate] &" # >= #" & Forms!frmlogdate!logfile &<br> ".logdate# And #" & [Forms]![frmlogdate].[EndingDate] & "# <= #" &<br> Forms!frmlogdate!logfile & ".logdate#"<br><br><br>I removed any spaces before the # but it still gives me a syntax error<br>Any other suggestions. Thanks <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>
Typically the text of the syntax error message will display the query string that caused the error. I cannnot see that and cannot identify the exact error. A couple of questions if I may. Is this query being executed from the form frmlogdate?<br><br>If it is remove the line "Forms![frmlogdate].[BeginningDate] and replace it with, "[BeginningDate]"<br><br>Is there more than one logfile? If not replace <br>"Forms!frmlogdate!logfile" with the name of the 'logfile' table<br><br>It should now look like this:<br><br>"SELECT * from tblLogFile where" & _ <br>"Initials = """ & [INITIALS] & """ and Beginningdate >=#" & [BeginningDate] & "# and logdate <= #" & [enddate] & "#"<br><br><br>-----------------------------------------------------------<br>If the above two questions are not the case you will have to play with the quotation marks for the two items replacing tblLogFile with the string "Forms![frmlogdate]!Controls.[BeginningDate]"; notice the placement of the dot. The statement extracting null values was removed all together, it's not necessary.<br><br>Alse I noticed the statement <br>"!Initials = """ & Forms![frmlogdate]![INITIALS] & """"<br>this can be misleading '!Initials' is part of a literal string being compared against the value [INTIALS] from the form. If the literal string "!Initials" never matches the literal string [INTIALS] on the form, it'll be ok. It won't cause an error and it also won't be doing what we may think it's doing.<br><br>I'm afraid that I'd have to have the .mdb and see exactly what you want to accomplish to handle this one.<br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>
- Status
Similar threads
- Locked
- Question
