Opening Bound form with criteria (Access Project)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I recently converted to a SQL server and Access 2000 Project and my Do.Cmd
Openform no longer works. I have made several attempts with different
variations but still can't seem to get the form to open heres my last failed
attempt:
DoCmd.OpenForm stDocName, , , "DateFlown = " +
'[forms]![frmSwitchboard].[txtText1]'

stDocName is assigned the correct form name and txtText1 has a static date
in it. The error I get says I have a syntax error which doesn't surprise me .
Can someone please show me how SQL server needs to see the WHERE statement.
Thanks
 
Try this, for date filter you need to add # before and after the value

DoCmd.OpenForm stDocName, , , "DateFlown = #" &
[forms]![frmSwitchboard].[txtText1] & "#"
 
Still get the Invalid SQL statement. That Syntax worked just fine when I was
just using Access as an mdb but now that I converted to SQL server and ACcess
Project it doesn't work. I believe the WHERE statement has to be written in
Transact SQL but Iv'e tried many variations with no luck.

Ofer said:
Try this, for date filter you need to add # before and after the value

DoCmd.OpenForm stDocName, , , "DateFlown = #" &
[forms]![frmSwitchboard].[txtText1] & "#"

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



AkAlan said:
I recently converted to a SQL server and Access 2000 Project and my Do.Cmd
Openform no longer works. I have made several attempts with different
variations but still can't seem to get the form to open heres my last failed
attempt:
DoCmd.OpenForm stDocName, , , "DateFlown = " +
'[forms]![frmSwitchboard].[txtText1]'

stDocName is assigned the correct form name and txtText1 has a static date
in it. The error I get says I have a syntax error which doesn't surprise me .
Can someone please show me how SQL server needs to see the WHERE statement.
Thanks
Click to expand...
Click to expand...
 
SQL Server uses ' as a delimiter, not #, and it (usually) wants the date in
yyyy-mm-dd format.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



AkAlan said:
Still get the Invalid SQL statement. That Syntax worked just fine when I
was
just using Access as an mdb but now that I converted to SQL server and
ACcess
Project it doesn't work. I believe the WHERE statement has to be written
in
Transact SQL but Iv'e tried many variations with no luck.

Ofer said:
Try this, for date filter you need to add # before and after the value

DoCmd.OpenForm stDocName, , , "DateFlown = #" &
[forms]![frmSwitchboard].[txtText1] & "#"

--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



AkAlan said:
I recently converted to a SQL server and Access 2000 Project and my
Do.Cmd
Openform no longer works. I have made several attempts with different
variations but still can't seem to get the form to open heres my last
failed
attempt:
DoCmd.OpenForm stDocName, , , "DateFlown = " +
'[forms]![frmSwitchboard].[txtText1]'

stDocName is assigned the correct form name and txtText1 has a static
date
in it. The error I get says I have a syntax error which doesn't
surprise me .
Can someone please show me how SQL server needs to see the WHERE
statement.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Try

DoCmd.OpenForm stDocName, , , "DateFlown = '" &
format( [forms]![frmSwitchboard].[txtText1], "yyyy-mm-dd") & "'"

Sql does not want the #'s. It likes a Single Quote, and the Sql default
date format is "yyyy-mm-dd"

--
Ron W
www.WorksRite.com
AkAlan said:
Still get the Invalid SQL statement. That Syntax worked just fine when I was
just using Access as an mdb but now that I converted to SQL server and ACcess
Project it doesn't work. I believe the WHERE statement has to be written in
Transact SQL but Iv'e tried many variations with no luck.

Ofer said:
Try this, for date filter you need to add # before and after the value

DoCmd.OpenForm stDocName, , , "DateFlown = #" &
[forms]![frmSwitchboard].[txtText1] & "#"

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



AkAlan said:
I recently converted to a SQL server and Access 2000 Project and my Do.Cmd
Openform no longer works. I have made several attempts with different
variations but still can't seem to get the form to open heres my last failed
attempt:
DoCmd.OpenForm stDocName, , , "DateFlown = " +
'[forms]![frmSwitchboard].[txtText1]'

stDocName is assigned the correct form name and txtText1 has a static date
in it. The error I get says I have a syntax error which doesn't surprise me .
Can someone please show me how SQL server needs to see the WHERE statement.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks Ron that worked great!!! It was the formating I didn't know about.
Ron Weiner said:
Try

DoCmd.OpenForm stDocName, , , "DateFlown = '" &
format( [forms]![frmSwitchboard].[txtText1], "yyyy-mm-dd") & "'"

Sql does not want the #'s. It likes a Single Quote, and the Sql default
date format is "yyyy-mm-dd"

--
Ron W
www.WorksRite.com
AkAlan said:
Still get the Invalid SQL statement. That Syntax worked just fine when I was
just using Access as an mdb but now that I converted to SQL server and ACcess
Project it doesn't work. I believe the WHERE statement has to be written in
Transact SQL but Iv'e tried many variations with no luck.

Ofer said:
Try this, for date filter you need to add # before and after the value

DoCmd.OpenForm stDocName, , , "DateFlown = #" &
[forms]![frmSwitchboard].[txtText1] & "#"

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I recently converted to a SQL server and Access 2000 Project and my Do.Cmd
Openform no longer works. I have made several attempts with different
variations but still can't seem to get the form to open heres my last failed
attempt:
DoCmd.OpenForm stDocName, , , "DateFlown = " +
'[forms]![frmSwitchboard].[txtText1]'

stDocName is assigned the correct form name and txtText1 has a static date
in it. The error I get says I have a syntax error which doesn't surprise me .
Can someone please show me how SQL server needs to see the WHERE statement.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top