Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 20th, 2008, 02:08 PM
Authorized User
 
Join Date: Mar 2008
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Another newb syntax question

Another newb here with a syntax issue. This works fine without the
 And "[Shift]='A'"

I've tried several variations and can't seem to make this work. Please help.


Code:
If IsNull(Me.PVCStartDate) Or IsNull(Me.PVCEndDate) Then
        MsgBox "Please enter dates to include in report"
    Else
DoCmd.OpenReport "rptPVCProd", acViewPreview, , "[PDate] Between #" & Format(Me.PVCStartDate, "mm\/dd\/yyyy") & "# And #" & Format(Me.PVCEndDate, "mm\/dd\/yyyy") & "#" And "[Shift]='A'"

End If


 
Old March 24th, 2008, 07:57 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Try this:

...
Dim sLink As String
...

sLink = "[PDate] Between #" & Format(Me.PVCStartDate, "mm\/dd\/yyyy") & "# And #" & Format(Me.PVCEndDate, "mm\/dd\/yyyy") & "#" And [Shift]='A'"

DoCmd.OpenReport "rptPVCProd", acViewPreview, , sLink

Please note that you had an extra " before the [Shift] entry, which closed the string and left out the [Shift] parameter, so your string would have been passed something like:

"[PDate] Between #01/01/2008# And #02/01/2008# And "

Instead of:

"[PDate] Between #01/01/2008# And #02/01/2008# And [Shift]='A'"

Generally it is not a good idea to pass parameters at runtime, so I added a string called sLink, packaged your criteria in sLink, and then put the finished string in the WHERE clause of the DoCmd. line. This is better practice since Access can get funky when you start to build a string in runtime given the order of operations.

HTH


mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old March 24th, 2008, 07:59 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, sLink should be:

sLink = "[PDate] Between #" & Format(Me.PVCStartDate, "mm\/dd\/yyyy") & "# And #" & Format(Me.PVCEndDate, "mm\/dd\/yyyy") & "# And [Shift]='A'"


mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old March 24th, 2008, 09:15 AM
Authorized User
 
Join Date: Mar 2008
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much. It's always something simple. Stupid ".

Adding the string works better, it doesn't take as long for the report to pull up. Which is good as there are several queries running to pull the data.
 
Old April 4th, 2008, 07:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Note that formatting is not relevant to the computer for passing the where condition, so sLink should be simply

sLink = "[PDate] Between #" & Me.PVCStartDate & "# And #" & Me.PVCEndDate & "# And [Shift] = 'A'"

Formatting is relevant only to the viewer of a form or report.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Syntax question ThomasWikman BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 4 May 7th, 2007 01:25 AM
Newb question zenobr BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 November 15th, 2006 10:19 PM
syntax question Tachyon Beginning PHP 4 July 14th, 2004 07:40 PM
Syntax question please. savoym VBScript 2 August 12th, 2003 12:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.