Using Switchboard - how can I have user enter a date range for a r

[Lorina]

Hi
I have a shipping report set up using switchboard. It's hundreds of pages
long and I want the user to be able to enter a date range to run the report
for (from 09/10/2009 to 09/10/2009). I don't know how to make that happen.
Any suggestions?

 

[Duane Hookom]

I would expect you could add two text boxes to the switchboard form named
txtStartDate and txtEndDate. Then assuming you have code to open the report
and a field in the report named [ShipDate], your code might end up looking
like:

Dim strWhere as String
Dim strReportName as String
strReportName = "rptShipping"
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND ShipDate >=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND ShipDate <=#" & _
Me.txtEndDate & "# "
End If
DoCmd.OpenReport strReportName, acPreview, , strWhere

 

[Lorina]

Thanks Duane - I'm affraid that that is all over my head. Is there a simple
way to do it? I don't even know where to enter the code that you provided!

I would expect you could add two text boxes to the switchboard form named
txtStartDate and txtEndDate. Then assuming you have code to open the report
and a field in the report named [ShipDate], your code might end up looking
like:

Dim strWhere as String
Dim strReportName as String
strReportName = "rptShipping"
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND ShipDate >=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND ShipDate <=#" & _
Me.txtEndDate & "# "
End If
DoCmd.OpenReport strReportName, acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP

Hi
I have a shipping report set up using switchboard. It's hundreds of pages
long and I want the user to be able to enter a date range to run the report
for (from 09/10/2009 to 09/10/2009). I don't know how to make that happen.
Any suggestions?

 

[Duane Hookom]

Did you at least add the text boxes to your switchboard form?
Is there a command button to open your report?
What version of Access are you using?

--
Duane Hookom
Microsoft Access MVP

Thanks Duane - I'm affraid that that is all over my head. Is there a simple
way to do it? I don't even know where to enter the code that you provided!

I would expect you could add two text boxes to the switchboard form named
txtStartDate and txtEndDate. Then assuming you have code to open the report
and a field in the report named [ShipDate], your code might end up looking
like:

Dim strWhere as String
Dim strReportName as String
strReportName = "rptShipping"
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND ShipDate >=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND ShipDate <=#" & _
Me.txtEndDate & "# "
End If
DoCmd.OpenReport strReportName, acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP

Hi
I have a shipping report set up using switchboard. It's hundreds of pages
long and I want the user to be able to enter a date range to run the report
for (from 09/10/2009 to 09/10/2009). I don't know how to make that happen.
Any suggestions?

 

[Lorina]

I have the report set up on Switchboard - but that would select all 600
pages. Is that the switchboard form that I should try to add text boxes
too? You mention a switchboard form - do I create a form just to add the
text boxes to? If so, how do I tie it to the report?

Did you at least add the text boxes to your switchboard form?
Is there a command button to open your report?
What version of Access are you using?

--
Duane Hookom
Microsoft Access MVP

Thanks Duane - I'm affraid that that is all over my head. Is there a simple
way to do it? I don't even know where to enter the code that you provided!

I would expect you could add two text boxes to the switchboard form named
txtStartDate and txtEndDate. Then assuming you have code to open the report
and a field in the report named [ShipDate], your code might end up looking
like:

Dim strWhere as String
Dim strReportName as String
strReportName = "rptShipping"
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND ShipDate >=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND ShipDate <=#" & _
Me.txtEndDate & "# "
End If
DoCmd.OpenReport strReportName, acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP


:

Hi
I have a shipping report set up using switchboard. It's hundreds of pages
long and I want the user to be able to enter a date range to run the report
for (from 09/10/2009 to 09/10/2009). I don't know how to make that happen.
Any suggestions?

 

[Lorina]

I am using Access 2003

Did you at least add the text boxes to your switchboard form?
Is there a command button to open your report?
What version of Access are you using?

--
Duane Hookom
Microsoft Access MVP

Thanks Duane - I'm affraid that that is all over my head. Is there a simple
way to do it? I don't even know where to enter the code that you provided!

I would expect you could add two text boxes to the switchboard form named
txtStartDate and txtEndDate. Then assuming you have code to open the report
and a field in the report named [ShipDate], your code might end up looking
like:

Dim strWhere as String
Dim strReportName as String
strReportName = "rptShipping"
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND ShipDate >=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND ShipDate <=#" & _
Me.txtEndDate & "# "
End If
DoCmd.OpenReport strReportName, acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP


:

Hi
I have a shipping report set up using switchboard. It's hundreds of pages
long and I want the user to be able to enter a date range to run the report
for (from 09/10/2009 to 09/10/2009). I don't know how to make that happen.
Any suggestions?

 

[Duane Hookom]

Can you add text boxes to your switchboard form? If not, just create a new
form with the two text boxes. Then use the command button wizard to create a
button that opens your report. After wizard is finished, modify the code
created by the wizard to resemble what I suggested. Then set up your
switchboard to open the new form rather than the report.

--
Duane Hookom
Microsoft Access MVP

I have the report set up on Switchboard - but that would select all 600
pages. Is that the switchboard form that I should try to add text boxes
too? You mention a switchboard form - do I create a form just to add the
text boxes to? If so, how do I tie it to the report?

Did you at least add the text boxes to your switchboard form?
Is there a command button to open your report?
What version of Access are you using?

--
Duane Hookom
Microsoft Access MVP

Thanks Duane - I'm affraid that that is all over my head. Is there a simple
way to do it? I don't even know where to enter the code that you provided!

:

I would expect you could add two text boxes to the switchboard form named
txtStartDate and txtEndDate. Then assuming you have code to open the report
and a field in the report named [ShipDate], your code might end up looking
like:

Dim strWhere as String
Dim strReportName as String
strReportName = "rptShipping"
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND ShipDate >=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND ShipDate <=#" & _
Me.txtEndDate & "# "
End If
DoCmd.OpenReport strReportName, acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP


:

Hi
I have a shipping report set up using switchboard. It's hundreds of pages
long and I want the user to be able to enter a date range to run the report
for (from 09/10/2009 to 09/10/2009). I don't know how to make that happen.
Any suggestions?

 

[Lorina]

Duane -
I set up a form to enter the to and from date, then set up a macro to run
the report. It worked great the first time. But now everytime I enter a
different date range it still gives me the ranget hat I first entered! Any
clues what I can change?

 

[Duane Hookom]

I don't use macros. Can you share the code that was created by the command
button wizard?

 

[Lorina]

there was no wizard - I had to do it by hand. Tell me how to find the code
and I will tell you what it says.

 

[Duane Hookom]

When in design view of a form, you can use the command button wizard from the
tool box to create a button that will open the report.

 

[Lorina]

Duane -
I am SO CLOSE!! I started from scratch and name my report and my text
fields the same as your example (just to be sure!). It will now recogonize
the first date range but not the 2nd (for example, if I use 09/15/2009 to
09/15/2009 the report starts with orders for 9/15 but goes all the way out to
1/11/2010). Here is my code - can you tell what I did wrong? ( I copy and
pasted the entire code screen below)

Option Compare Database

Private Sub Command5_Click()
Dim strWhere As String
Dim strReportName As String
strReportName = "shipping"
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "AND DESIRED_SHIP_DATE>= #" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
srtWhere = strWhere & " And DESIRED_SHIP_DATE <=#" & _
Me.txtEndDate & "# "
End If
DoCmd.OpenReport strReportName, acViewPreview, , strWhere





End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()

End Sub

Private Sub txtStartDate_BeforeUpdate(Cancel As Integer)

End Sub

 

[Duane Hookom]

You have a spelling error. Try to always use Option Explicit
Option Compare Database
Option Explicit 'add this line to find spelling errors

If you compile your code, you will find an error where you have typed
srtWhere rather than strWhere.

 

[Lorina]

Works perfectly! Thanks for all your help

You have a spelling error. Try to always use Option Explicit
Option Compare Database
Option Explicit 'add this line to find spelling errors

If you compile your code, you will find an error where you have typed
srtWhere rather than strWhere.

 

[Lorina]

Duane -
Now I need to create a form that will bring up data based on customer Id
insted of date. I used the same code and changed the date references to cust
id. I thought that might work. But...it didn't. When I run the report I
get this error:
Run-Time error '3075':
Syntax error in date in query expression '(1 =1 and CUSTOMER_ID > = #5028#
and Customer_ID <= #50028#)'.

Here is my code:
Option Compare Database

Private Sub Command5_Click()
Dim strWhere As String
Dim strReportName As String
strReportName = "shipping"
strWhere = "1=1 "
If Not IsNull(Me.txtStartCustId) Then
strWhere = strWhere & "AND CUSTOMER_ID>= #" & _
Me.txtStartCustId & "# "
End If
If Not IsNull(Me.txtEndCustId) Then
strWhere = strWhere & " And CUSTOMER_ID <=#" & _
Me.txtEndCustId & "# "
End If
DoCmd.OpenReport strReportName, acViewPreview, , strWhere





End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()

End Sub

Private Sub txtStartDate_BeforeUpdate(Cancel As Integer)

End Sub

 

[Duane Hookom]

The "#" are used to delimit date values in Access/JET. If Customer_ID is
numeric, remove all delimiters. If Customer_ID is text, replace each # with a
single quote '.

 

[Lorina]

I figured it was something like that . Thanks! One more question - in the
very bottom of the one I am copying from it shows this line:
nd Sub

Private Sub txtStartDate_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Form_Load()

Should I change txtStartDate _BeforeUpdate to txtCustId_BeforeUpdate or do I
need something completely different?

 

[Duane Hookom]

i would not run the code in the before or after update event of a control.
Generally, I add a command button to build the where clause and open the
report.

 

[Lorina]

Ok - got that to work. Thanks! Is it possible to have a form where I have
multiple parameters? For example, Customer ID range and then that data by
date range specified by the user? What would that code look like? Just
append the date range into the code that specifies the cust id range?

 

[Duane Hookom]

The code I suggested already includes a couple parameters from form controls.
You can add as many as you want:

Dim strWhere as String
Dim strReportName as String
strReportName = "rptShipping"
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND ShipDate >=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND ShipDate <=#" & _
Me.txtEndDate & "# "
End If
' assuming a numeric field named CustomerID
If Not IsNull(Me.txtStartCustID) Then
strWhere = strWhere & " AND CustomerID >=" & _
Me.txtStartCustID
End If
If Not IsNull(Me.txtEndCustID) Then
strWhere = strWhere & " AND CustomerID <=" & _
Me.txtEndCustID
End If

DoCmd.OpenReport strReportName, acPreview, , strWhere