Set date range and filter results via form/menu for Report Preview

I created a form to use as a "print menu" for a report. You can select
the date range (txtStartDate and txtStopDate). I have this part of the
code working. However, I also need to be able to filter based on the
job flag: [Flag] with value of 1, 2 or 3-- OR show all (still applying
the date range). I created a list box on the form and named it
[ReportFilter]. It's bound to the first column (hidden) with 0 for
All, 1, 2 and 3 to correspond with the Flag names. I can't figure out
how to get All records to show on top of not being able to incorporate
with the above date range.

Here's what I have so far. How would I modify?

Private Sub btnViewRpt_Click()
    Dim strReport As String
    Dim strField As String
    Dim strWhere As String
    Const conDateFormat = "\#mm\/dd\/yyyy\#"

    strReport = "rptJobsbyCustInclCanc_All"
    strField = "Callin"

    If IsNull(Me.txtStartDate) Then
        If Not IsNull(Me.txtEndDate) Then
            strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
        End If
    Else
        If IsNull(Me.txtEndDate) Then
            strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
        Else
            strWhere = strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
                & " And " & Format(Me.txtEndDate, conDateFormat)
        End If
    End If

    ' Debug.Print strWhere
    DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub


Any help anyone can offer will be greatly appreciated!

Thanks,
Ruth

0
Ruth
4/25/2007 9:33:46 PM
access 16762 articles. 3 followers. Follow

12 Replies
1018 Views

Similar Articles

[PageSpeed] 48

Ruth wrote:
> I created a form to use as a "print menu" for a report. You can select
> the date range (txtStartDate and txtStopDate). I have this part of the
> code working. However, I also need to be able to filter based on the
> job flag: [Flag] with value of 1, 2 or 3-- OR show all (still applying
> the date range). I created a list box on the form and named it
> [ReportFilter]. It's bound to the first column (hidden) with 0 for
> All, 1, 2 and 3 to correspond with the Flag names. I can't figure out
> how to get All records to show on top of not being able to incorporate
> with the above date range.
> 
> Here's what I have so far. How would I modify?
> 
> Private Sub btnViewRpt_Click()
>     Dim strReport As String
>     Dim strField As String
>     Dim strWhere As String
>     Const conDateFormat = "\#mm\/dd\/yyyy\#"
> 
>     strReport = "rptJobsbyCustInclCanc_All"
>     strField = "Callin"
> 
>     If IsNull(Me.txtStartDate) Then
>         If Not IsNull(Me.txtEndDate) Then
>             strWhere = strField & " <= " & Format(Me.txtEndDate,
> conDateFormat)
>         End If
>     Else
>         If IsNull(Me.txtEndDate) Then
>             strWhere = strField & " >= " & Format(Me.txtStartDate,
> conDateFormat)
>         Else
>             strWhere = strField & " Between " &
> Format(Me.txtStartDate, conDateFormat) _
>                 & " And " & Format(Me.txtEndDate, conDateFormat)
>         End If
>     End If
> 
>     ' Debug.Print strWhere
>     DoCmd.OpenReport strReport, acViewPreview, , strWhere
> 
> End Sub
> 
> 
> Any help anyone can offer will be greatly appreciated!
> 
> Thanks,
> Ruth

Try this modified version (watch for line wrapping):

Private Sub btnViewRpt_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptJobsbyCustInclCanc_All"
strField = "Callin"

strWhere = ""
If IsNull(Me.txtStartDate) Then
   If Not IsNull(Me.txtEndDate) Then
     strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
   End If
Else
   If IsNull(Me.txtEndDate) Then
     strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
   Else
     strWhere = strField & " Between " & Format(Me.txtStartDate, 
conDateFormat) _
     & " And " & Format(Me.txtEndDate, conDateFormat)
   End If
End If
If Not IsNull(Me!ReportFilter.Value) Then
   If Me!ReportFilter.Value <> 0 Then
     If strWhere <> "" Then
       strWhere = "(" & strWhere & ") AND [Flag] = " & Chr(34) & 
CStr(Me!ReportFilter.Value) & Chr(34)
     Else
       strWhere = "[Flag] = " & Chr(34) & CStr(Me!ReportFilter.Value) & 
Chr(34)
     End If
   End If
End If
' Debug.Print strWhere
If strWhere <> "" Then
   DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
   DoCmd.OpenReport strReport, acViewPreview
End If
End Sub

James A. Fortune
MPAPoster@FortuneJames.com
0
James
4/26/2007 2:33:37 AM
Thanks, James. I appreciate your help!

Adding the code, I was able to get it to run by adding another End If
right before End Sub (per debugger). I can get the report for "All"
but making any of the other selections gives this error:

Run-time error '3464':

Data type mismatch in criteria expression.

Suggestions?

Thanks ever so much for your help!

Ruth

0
Ruth
4/26/2007 2:20:24 PM
Ruth wrote:
> Thanks, James. I appreciate your help!
> 
> Adding the code, I was able to get it to run by adding another End If
> right before End Sub (per debugger).

I don't see where I missed any.

>                           I can get the report for "All"
> but making any of the other selections gives this error:
> 
> Run-time error '3464':
> 
> Data type mismatch in criteria expression.
> 
> Suggestions?
> 
> Thanks ever so much for your help!
> 
> Ruth
> 

If [Flag] is a numeric field then don't use the Chr(34) values.  Compare 
it directly to the number.  E.g.,

     strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)

I hope that helps.  If not, post the code you are now running along with 
the field types.

James A. Fortune
MPAPoster@FortuneJames.com
0
James
4/27/2007 12:09:07 AM
On the missing EndIf, it was my fault! I either missed it in copying
or deleted it. I was going to post back when I realized it, but my
response hadn't posted to the forum yet. So-- you're right! You didn't
miss any.

I'm trying your suggestion now. I'll post back and let you know the
result. Thanks again for your help!

0
Ruth
4/27/2007 2:51:04 PM
Hi James,
It works beautifully!  ... for three of my choices... *sigh*

The [Flag] field is numeric (contents of the field are 1, 2, 3 or
null). I can get the appropriate report back if the choice is a 1, 2
or 3, but now I can't get the "All" report that delivered everything
in the date range if Me!ReportFilter.Value was left blank.

Here's the current code:

Private Sub Command0_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"



strReport = "rptJobDetailSumm-ALL"
strField = "Callin"

strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
If IsNull(Me.txtStartDate) Then
   If Not IsNull(Me.txtEndDate) Then
     strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
   End If
Else
   If IsNull(Me.txtEndDate) Then
     strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
   Else
     strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
     & " And " & Format(Me.txtEndDate, conDateFormat)
   End If
End If
If Not IsNull(Me!ReportFilter.Value) Then
   If Me!ReportFilter.Value <> 0 Then
     If strWhere <> "" Then
       strWhere = "(" & strWhere & ") AND [Flag] = " & CStr(Me!
ReportFilter.Value)
     Else
       strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
     End If
   End If
End If
' Debug.Print strWhere
If strWhere <> "" Then
   DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
   DoCmd.OpenReport strReport, acViewPreview
End If
End Sub


Thanks so very, very much!!!
Ruth


0
Ruth
4/27/2007 5:38:43 PM
Ruth wrote:
> Hi James,
> It works beautifully!  ... for three of my choices... *sigh*
> 
> The [Flag] field is numeric (contents of the field are 1, 2, 3 or
> null). I can get the appropriate report back if the choice is a 1, 2
> or 3, but now I can't get the "All" report that delivered everything
> in the date range if Me!ReportFilter.Value was left blank.
> 
> Here's the current code:
> 
> Private Sub Command0_Click()
> Dim strReport As String
> Dim strField As String
> Dim strWhere As String
> Const conDateFormat = "\#mm\/dd\/yyyy\#"
> 
> 
> 
> strReport = "rptJobDetailSumm-ALL"
> strField = "Callin"
> 
> strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
> If IsNull(Me.txtStartDate) Then
>    If Not IsNull(Me.txtEndDate) Then
>      strWhere = strField & " <= " & Format(Me.txtEndDate,
> conDateFormat)
>    End If
> Else
>    If IsNull(Me.txtEndDate) Then
>      strWhere = strField & " >= " & Format(Me.txtStartDate,
> conDateFormat)
>    Else
>      strWhere = strField & " Between " & Format(Me.txtStartDate,
> conDateFormat) _
>      & " And " & Format(Me.txtEndDate, conDateFormat)
>    End If
> End If
> If Not IsNull(Me!ReportFilter.Value) Then
>    If Me!ReportFilter.Value <> 0 Then
>      If strWhere <> "" Then
>        strWhere = "(" & strWhere & ") AND [Flag] = " & CStr(Me!
> ReportFilter.Value)
>      Else
>        strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
>      End If
>    End If
> End If
> ' Debug.Print strWhere
> If strWhere <> "" Then
>    DoCmd.OpenReport strReport, acViewPreview, , strWhere
> Else
>    DoCmd.OpenReport strReport, acViewPreview
> End If
> End Sub
> 
> 
> Thanks so very, very much!!!
> Ruth

I see that you made some changes to what I suggested.  Plus, originally 
you used 0 instead of Null for the 'All' case.  What you want to have 
happen is for there to be no restrictions whatever on [Flag] when the 
'All' case is chosen.  The Null case in the code I suggested was simply 
to handle someone forgetting to choose a [Flag].  Anyway, make sure that 
a [Flag] criterion doesn't become a part of strWhere at all for the 
'All' case.  Perhaps use your debug line to view what you are getting 
for strWhere.

James A. Fortune
MPAPoster@FortuneJames.com
0
James
4/27/2007 10:02:42 PM
James, I apologize for the changes I made. They were inadvertant.
However, here's the completed code and it works perfectly!  Thanks
again so much for your help. If you're up for it, I have one more
variable I'd like to incorporate. I don't want to tread on your
goodwill, so please let me know if I can post a further question.

Thanks again!

Private Sub btnViewRpt_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"



strReport = "rptJobsbyCustInclCanc"
strField = "Callin"

strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
If IsNull(Me.txtStartDate) Then
   If Not IsNull(Me.txtEndDate) Then
     strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
   End If
Else
   If IsNull(Me.txtEndDate) Then
     strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
   Else
     strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
     & " And " & Format(Me.txtEndDate, conDateFormat)
   End If
End If
If Not IsNull(Me!ReportFilter.Value) Then
   If Me!ReportFilter.Value <> 0 Then
     If strWhere <> "" Then
       strWhere = "(" & strWhere & ") AND [Flag] = " & CStr(Me!
ReportFilter.Value)
     Else
       strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
     End If
   End If
End If
' Debug.Print strWhere
If strWhere <> "" Then
   DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
   DoCmd.OpenReport strReport, acViewPreview

End If

End Sub


0
Ruth
4/30/2007 7:02:47 PM
Ruth wrote:
> James, I apologize for the changes I made. They were inadvertant.
> However, here's the completed code and it works perfectly!  Thanks
> again so much for your help. If you're up for it, I have one more
> variable I'd like to incorporate. I don't want to tread on your
> goodwill, so please let me know if I can post a further question.
> 
> Thanks again!
> 
> Private Sub btnViewRpt_Click()
> Dim strReport As String
> Dim strField As String
> Dim strWhere As String
> Const conDateFormat = "\#mm\/dd\/yyyy\#"
> 
> 
> 
> strReport = "rptJobsbyCustInclCanc"
> strField = "Callin"
> 
> strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
> If IsNull(Me.txtStartDate) Then
>    If Not IsNull(Me.txtEndDate) Then
>      strWhere = strField & " <= " & Format(Me.txtEndDate,
> conDateFormat)
>    End If
> Else
>    If IsNull(Me.txtEndDate) Then
>      strWhere = strField & " >= " & Format(Me.txtStartDate,
> conDateFormat)
>    Else
>      strWhere = strField & " Between " & Format(Me.txtStartDate,
> conDateFormat) _
>      & " And " & Format(Me.txtEndDate, conDateFormat)
>    End If
> End If
> If Not IsNull(Me!ReportFilter.Value) Then
>    If Me!ReportFilter.Value <> 0 Then
>      If strWhere <> "" Then
>        strWhere = "(" & strWhere & ") AND [Flag] = " & CStr(Me!
> ReportFilter.Value)
>      Else
>        strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
>      End If
>    End If
> End If
> ' Debug.Print strWhere
> If strWhere <> "" Then
>    DoCmd.OpenReport strReport, acViewPreview, , strWhere
> Else
>    DoCmd.OpenReport strReport, acViewPreview
> 
> End If
> 
> End Sub

O.K., I think I understand the changes you made enough to add another 
variable to the mix.

But change the first strWhere to:

strWhere = "[Flag] = " & CStr(Nz(Me!ReportFilter.Value, 0))

so that you don't get an error if the user forgets to make a choice.

James A. Fortune
MPAPoster@FortuneJames.com
0
James
4/30/2007 11:22:31 PM
I'm sorry for being confusing! I actually have 4 menus for different
reports and I'm using the same code to drive all of them. Which was
going to take me to my next "variable" question... whether I could
populate "strReport" with a value from the menu. I think I probably
can....

But instead of doing that, I'd like to add two more combo boxes as
variables.  cboCustomer and cboAircraftType. I've gotten close (I
think) but my syntax is still off. Both are text fields. They are on
the form below the ReportFilter field.

Re: your edit below, thank you! I had changed out the string when I
was making the Chr(34) change and didn't notice until it was too late!
Thanks again for catching that.
|   |  |   |
v  v  v  v
>
> But change the first strWhere to:
>
> strWhere = "[Flag] = " & CStr(Nz(Me!ReportFilter.Value, 0))
>
> so that you don't get an error if the user forgets to make a choice.
>

***********************************************************************

Thanks again for your help!

Ruth

0
Ruth
5/1/2007 7:14:54 PM
Ruth wrote:
> I'm sorry for being confusing! I actually have 4 menus for different
> reports and I'm using the same code to drive all of them. Which was
> going to take me to my next "variable" question... whether I could
> populate "strReport" with a value from the menu. I think I probably
> can....
> 
> But instead of doing that, I'd like to add two more combo boxes as
> variables.  cboCustomer and cboAircraftType. I've gotten close (I
> think) but my syntax is still off. Both are text fields. They are on
> the form below the ReportFilter field.
> 
> Re: your edit below, thank you! I had changed out the string when I
> was making the Chr(34) change and didn't notice until it was too late!
> Thanks again for catching that.
> |   |  |   |
> v  v  v  v
> 
>>But change the first strWhere to:
>>
>>strWhere = "[Flag] = " & CStr(Nz(Me!ReportFilter.Value, 0))
>>
>>so that you don't get an error if the user forgets to make a choice.
>>
> 
> 
> ***********************************************************************
> 
> Thanks again for your help!
> 
> Ruth
> 

Although it's possible to open one of several different reports from 
your code depending on which values the user selects, one of the 
beauties of creating SQL dynamically is that several different scenarios 
can be handled by a single report.  For example, you can set the Control 
Source for a text box on the report to something like:

="Report Flag: " & IIf(Not 
IsNull([Forms]![frmReportFilter]!ReportFilter.Column(1)), 
[Forms]![frmReportFilter]!ReportFilter.Column(1), "All")

Since you always have the [Flag] criterion in strWhere, you can tack on 
cboCustomer and cboAircraftType to the SQL criteria if they contain values:

If Not IsNull(cbxCustomer.Value) Then
   strWhere = strWhere & " AND [Customer] = " & Chr(34) & 
cbxCustomer.Value & Chr(34)
End If
If Not IsNull(cbxAircraftType.Value) Then
   strWhere = strWhere & " AND [AircraftType] = " & Chr(34) & 
cbxAircraftType.Value & Chr(34)
End If

or if you have ID's in column 0 as before:

If Not IsNull(cbxCustomer.Column(1)) Then
   strWhere = strWhere & " AND [Customer] = " & Chr(34) & 
cbxCustomer.Column(1) & Chr(34)
End If
If Not IsNull(cbxAircraftType.Column(1)) Then
   strWhere = strWhere & " AND [AircraftType] = " & Chr(34) & 
cbxAircraftType.Column(1) & Chr(34)
End If

Obviously, [Customer] and [AircraftType] should be changed to your 
actual field names if they are different.  You can double check the 
final strWhere using your debug statement.  You can also write out 
strWhere to a text file so that you can test out the SQL separately in 
the Query By Example (QBE) query design area by creating a new query, 
clicking on the SQL toolbar, then pasting the text for your query:

SELECT * FROM MyReportTable WHERE ...;

.... = strWhere text

James A. Fortune
MPAPoster@FortuneJames.com
0
James
5/1/2007 8:49:25 PM
Well darn. I KNOW I replied to this message this morning, but
evidently my post was eaten for breakfast!

James, I'm missing something. The code runs without error, debugging
doesn't show anything, but the additional combo boxes don't filter the
data any further.

Here's the code as I have it currently:

Private Sub Command0_Click()

Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptJobDetailSumm"
strField = "Callin"
strWhere = "[Flag] = " & CStr(Nz(Me!ReportFilter.Value, 0))
If IsNull(Me.txtStartDate) Then
   If Not IsNull(Me.txtEndDate) Then
     strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
   End If
Else
   If IsNull(Me.txtEndDate) Then
     strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
   Else
     strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
     & " And " & Format(Me.txtEndDate, conDateFormat)
   End If
End If
If Not IsNull(Me!ReportFilter.Value) Then
   If Me!ReportFilter.Value <> 0 Then
     If strWhere <> "" Then
       strWhere = "(" & strWhere & ") AND [Flag] = " & CStr(Me!
ReportFilter.Value)
     Else
       strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
     End If
   End If
End If
' Code works to this point

If Not IsNull(cboCustomer.Column(1)) Then
   strWhere = strWhere & " AND [CustomerName] = " & Chr(34) &
cboCustomer.Column(1) & Chr(34)

End If
If Not IsNull(cboACType.Column(1)) Then
   strWhere = strWhere & " AND [AircraftType] = " & Chr(34) &
cboACType.Column(1) & Chr(34)
End If
' End new code addition

' Debug.Print strWhere
If strWhere <> "" Then
   DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
   DoCmd.OpenReport strReport, acViewPreview
   End If
            Exit Sub

' Debug.Print strWhere
If strWhere <> "" Then
   DoCmd.OpenReport strReport, acViewPreview, , strWhere
Else
   DoCmd.OpenReport strReport, acViewPreview
   End If
            Exit Sub

End Sub


Regards,
Ruth

0
Ruth
5/2/2007 7:26:01 PM
Ruth wrote:
> Well darn. I KNOW I replied to this message this morning, but
> evidently my post was eaten for breakfast!
> 
> James, I'm missing something. The code runs without error, debugging
> doesn't show anything, but the additional combo boxes don't filter the
> data any further.
> 
> Here's the code as I have it currently:
> 
> Private Sub Command0_Click()
> 
> Dim strReport As String
> Dim strField As String
> Dim strWhere As String
> Const conDateFormat = "\#mm\/dd\/yyyy\#"
> strReport = "rptJobDetailSumm"
> strField = "Callin"
> strWhere = "[Flag] = " & CStr(Nz(Me!ReportFilter.Value, 0))
> If IsNull(Me.txtStartDate) Then
>    If Not IsNull(Me.txtEndDate) Then
>      strWhere = strField & " <= " & Format(Me.txtEndDate,
> conDateFormat)
>    End If
> Else
>    If IsNull(Me.txtEndDate) Then
>      strWhere = strField & " >= " & Format(Me.txtStartDate,
> conDateFormat)
>    Else
>      strWhere = strField & " Between " & Format(Me.txtStartDate,
> conDateFormat) _
>      & " And " & Format(Me.txtEndDate, conDateFormat)
>    End If
> End If
> If Not IsNull(Me!ReportFilter.Value) Then
>    If Me!ReportFilter.Value <> 0 Then
>      If strWhere <> "" Then
>        strWhere = "(" & strWhere & ") AND [Flag] = " & CStr(Me!
> ReportFilter.Value)
>      Else
>        strWhere = "[Flag] = " & CStr(Me!ReportFilter.Value)
>      End If
>    End If
> End If
> ' Code works to this point
> 
> If Not IsNull(cboCustomer.Column(1)) Then
>    strWhere = strWhere & " AND [CustomerName] = " & Chr(34) &
> cboCustomer.Column(1) & Chr(34)
> 
> End If
> If Not IsNull(cboACType.Column(1)) Then
>    strWhere = strWhere & " AND [AircraftType] = " & Chr(34) &
> cboACType.Column(1) & Chr(34)
> End If
> ' End new code addition
> 
> ' Debug.Print strWhere
> If strWhere <> "" Then
>    DoCmd.OpenReport strReport, acViewPreview, , strWhere
> Else
>    DoCmd.OpenReport strReport, acViewPreview
>    End If
>             Exit Sub
> 
> ' Debug.Print strWhere
> If strWhere <> "" Then
>    DoCmd.OpenReport strReport, acViewPreview, , strWhere
> Else
>    DoCmd.OpenReport strReport, acViewPreview
>    End If
>             Exit Sub
> 
> End Sub
> 
> 
> Regards,
> Ruth
> 

Ruth,

Try it with an additional comma before strWhere.  For an explanation, 
see Access Help for OpenReport.

James A. Fortune
MPAPoster@FortuneJames.com
0
James
5/4/2007 10:00:37 PM
Reply:

Similar Artilces:

Excel as a reporting tool
Hope you folks out there can help me out with suggestions here. I am about to write a management accounts reporting pack for a reasonably large company. Their trial balance is 16,000 lines long and downloads usually include This Year Month, This Year to date, Budget month, Budget YTD, Prior Year Month, Prior Year To date so its a fair bit of data. The reporting pack is likely to be 40 - 50 pages of analysis, summaries, P&L, Balance sheets, Cash Flows, etc., which read directly off the TB download. There would also be some intermediary calculations on some sheets (eg to provide a "...

height and width settings locked?
Can row height and column width settings be locked in an Excel document without blocking the user from entering/editing data in the cells? Only by the usual mechanism of unlocking the data entry cells, which could be all the cells. -- Jim "Lab Teacher" <Lab Teacher@discussions.microsoft.com> wrote in message news:F6D09DC1-B425-4032-9C17-120AECD498A7@microsoft.com... | Can row height and column width settings be locked in an Excel document | without blocking the user from entering/editing data in the cells? Click the button between row 1 and column A to select the enti...

Group Trial Balance Detailed & Summary Reports
Hi there, We have lots of Trial Balance Detailed & Summary reports (at least one for every balance sheet account) and I'd like to be able to group several TB reports to print at one time. Right now, GP's limitation is to show the first 32 TB Detailed/Summary Reports in alphabetical order. It won't show me the rest and so I am not able to pick the ones I want to group. This will help my productivity alot!! Thanks, -- V Who's Calling, Inc Financial Reporting Manager ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggest...

change then set the default paper size in publisher
the default paper size used in publisher when beginning a blank publication is letter. does anyone know how to change the default size to A4? Hi uncledazza (uncledazza@discussions.microsoft.com), in the newsgroups you posted: || the default paper size used in publisher when beginning a blank || publication is letter. does anyone know how to change the default || size to A4? Yes, you need to change your printer driver default to A4. In Windows, go to Start > Settings > Control Panel > Printers and then Right Click > Properties on your printer. Once in the property menu, change...

onchange event- UTC format for Date
I am using a picklist onchange event to change the date of another field. Date value is in the format of 'mm/dd/yyyy' in the script. But It's a format of returnValue field which takes UTC format. I tried Date.toUTCString() function but it did not work either. Can anyone tell me how to correctly format UTC date. If there is any function availabe please let me know. I will greatly appreciate help. thanks, One of my friend (Tim Darius) helped me giving the following code snippet: ====================== /* When a Case Type is selected, default the followupby date field to toda...

distribute access 2002 form to user with access 2000
I'm developing a startup form using access 2002 (but using access 2000 file format) - the form will not open correctly for a user with access 2000. I think it is due to their reference library 'Microsoft Access 9.0 Object Library' where I'm using 10.0 object library. I think the function that is causing the issue is this one (moving the location of a 'rectangle' control I'm calling newPoint). My question is this 1) How do I make this work in Access 2000? Can I package the Access 10.0 object library with the db? Is there another control that the 9.0 object libr...

Context Menu w/Popup missing initial menu text
Hello. I've been trying to find the answer to this on my own, but it's just not getting me anywhere. I've put in a context menu with a popup. Problem is, I can't get the first menu to display, but I can get the contents of the popup to show up. Here's the code I'm working with. BOOL CGUISnoopView::OnNotify(WPARAM wParam, LPARAM lParam, LRESULT* pResult) { CGUISnoopDoc* pDoc = GetDocument(); ASSERT_VALID(pDoc); if((((NMHDR*)lParam)->code ==NM_RCLICK)&&m_pGrid->GetFocusCell().row>0) { CMenu menu; VERIFY(menu.LoadMenu(IDR_POP_MENU)); CPoint pt;...

Check text in a range
Hi all I�m a new member & I�d appreciate help with the following: I have a vertical range of text which changes and which is linked t another sheet. I need to be alerted if a text appears more than tw times in the range. Any takers?? e.g. Bob Bob Tom Tom Jane Jane Bob �alert, a member is oversubscribed� Thanks Mik -- Message posted from http://www.ExcelForum.com > I�m a new member & I�d appreciate help with the following: > > I have a vertical range of text which changes and which is linked to > another sheet. I need to be alerted if a text appears more than tw...

Switchboard Form deleted, how do I get it back?
I was attempting to do something and I deleted the switchboard template form. I still have the stuff in the switchboard manager and the switchboard table, but I no longer have the form. Is there anyway to regenerate this form? I assume there is, but I have had not success finding it. Thanks in advance for help! David On 1 May 2007 10:24:28 -0700, DtTall wrote: > I was attempting to do something and I deleted the switchboard > template form. I still have the stuff in the switchboard manager and > the switchboard table, but I no longer have the form. > > Is there anyway to ...

i lost my menu bar in excel
I have lost everything at the top of my excel page. No edit, file, toolbars, or anything. The shortcut keys seem to work. I don't remember doing anything to it. Can anyone help? Brian hi, Go to View>toolbars Click the toolbars you want. this will get your tool bars back but i don't know what made them disappear. Regards Frank >-----Original Message----- >I have lost everything at the top of my excel page. No edit, file, toolbars, >or anything. The shortcut keys seem to work. I don't remember doing >anything to it. Can anyone help? >Brian >. >...

Filter & Copy
Hi, I have 15 sheet is one workbook. 12 of them represent months starting June 2006 until May 2007. the other 3 sheets are reports. For each month in the range AW201:AW249 either 1 or empty (nothing is entered). I need to copy all the lines that has the value 1 in the range AW201:AW249 from ALL the 12 month sheets to a new sheet in the same workbook. How can this be done using VBA code or even a Macro!!! Note: The sheets are protected. I am also using freeze pan option. Khalil, I just added a new worksheet manually (worksheet 16). I assumed that the monthly sheets are worksheets 1-12....

calendar option to choose a date in excel
I am trying to create a spreadsheet that includes a column in which you need to put a date. Can you have a drop-down calendar to choose from instead of typing the date? http://www.rondebruin.nl/calendar.htm -- Regards, Peo Sjoblom "Julie" <Julie@discussions.microsoft.com> wrote in message news:7DDDEC32-34B3-4078-BCA3-150B8D3A43EA@microsoft.com... > I am trying to create a spreadsheet that includes a column in which you need > to put a date. Can you have a drop-down calendar to choose from instead of > typing the date? ...

Form to open based on selection of another form
Hello, I would like some help on how to do the following: I have a form called "frmpreselect". In addition to this I have three other forms called "FINENTRY", "TBSENTRY", "PSHRMACENTRY". On form "frmpreselect" I have two fields, one being "DeptID" and "Document Type". These are both combo boxes. The form also has an open form command button. What I would like to have the form and button to work as is the following: If in field "DeptID", the user selects "FIN", then when he presses the open ...

Pay Vendor Reports
We've created a new company in GP/SBM. In the old company at the end of the Pay Vendor rountines we automatically 5 reports out. With the new company we only get 2. How can i either select or add reports to the end of that routine or any other for that matter? In Great Plains go to Tools - Setup - Posting. Choose the series you want (Purchasing) then the Origin (computer checks) At the bottom you will see a list of reports that print. Check the boxes for all of the reports you want. "Mike" wrote: > We've created a new company in GP/SBM. In the old company at t...

Filter on Current Record
Hello all... I have a form (formA) witha subform (formB). The subform is set to continuous records view. I would like to have a button within the subform which will open another form (formC) and filter formC by the currently selected record in formB. Now, I would have no problem with this if formB were set to Single View. In that case I could create a filter and use the criteria [forms]![formB]![Order Number] to accomplish what I want. But with the form view set to Continuous Records, the filter criteria doesn't understand which [Order Number] to use. Thanks in advance for yo...

HELP! PowerPoint Problems
PROBLEM REPORT Powerpoint version now in use: 2007 Version the file was created in or opened in since, if any: created in 2003, but the problems started while using 2003. I had hoped upgrading to 2007 would help, but it hasn't. It got worse. Windows version: Vista Other hardware involved (printer, etc): Just my laptop Description of problem: When I first got my laptop, I was excited because our church just got a projector and I couldn't wait to set up PowerPoint slideshows to run our church choruses with music and words altogether. I'd set up many slideshow...

combine set of letters randomly
hi, is it possible to combine 8 letters (for example) randomly, i mean to generate different combinations from these 8 letters? please help yob On Fri, 16 Sep 2005 15:57:40 +0800, "rp" <rp@hotmail.com> wrote in microsoft.public.excel: >is it possible to combine 8 letters (for example) randomly, i mean to >generate different combinations from these 8 letters? please help This has been covered not so long ago in <http://groups.google.com.au/group/microsoft.public.office.misc/browse_frm/thread/8d6139cc43ce052d/250c38d50a17e0dd>. Note that 8 letters will yield 4...

Help With Date Calculation Query
I have not used access in awhile. I have dumped a lot of data into an access database and I need to compare two date fields that would essentially need to measure the number of days between the invoice date and the payment date. I created the following expression, but cannot determind what I am doing wrong. [AP-Payment History Report]![CheckDate] "Date ()" - [AP-Payment History Report]![InvoiceDate] "Date ()" < "30" I guess I want the query results to show the invoices that were paid in less then thirty days. Am I saying that right. I have tried to...

Show formulas instead of results
I am trying to have the actual formulas show up on my spreadsheet, instead of the results. I think I have done it before, but I can't remember now. Thank you Tools>Options>View, check Formulas -- Kind regards, Niek Otten "Hippopotamus" <Hippopotamus@discussions.microsoft.com> wrote in message news:4248D92C-72E8-462D-B68C-71B3052E40DC@microsoft.com... >I am trying to have the actual formulas show up on my spreadsheet, instead >of > the results. I think I have done it before, but I can't remember now. > > Thank you goto tools and option...

List boxes in excel forms
Can someone please tell me how to enter values in an excel drop down list box? I am not an advanced user and am really struggling! Thanks Hi Liz! Two ways: Select the cell where you want the list box to be Data > Validation > Allow Select List Type in the list separated by commas. Type in your list in a range of cells Select the cell where you want the list box to be Data > Validation > List Type in the reference to your range of cells. If the range is not on the same sheet, you need to name it and refer to it by name. -- Regards Norman Harker MVP (Excel) Sydney, Austral...

passing a name of the form to a function
I have a function, and would like to pass a name of the form to it so I can search for a particular name of a page of the form: Public Sub Multipage_page_finder(form As String, page_name As String) MsgBox form MsgBox page_name For i = 0 To (Controls(form).MultiPage_main.pages.count - 1) If Controls(form).MultiPage_main.pages(i).Name = page_name Then 'MsgBox "found" Controls(form).MultiPage_main.Value = i End If Next i End Sub What I have to modify? On 19 Mar 2007 11:06:29 -0700, vukblagojevic@gmail.com wrote: >I have a function, and would like to...

Report "Spending by Category" reflects charges but not credits
MS Money 2005 Successfully uploaded two years data. While enjoying the pie chart graphs, I noticed that "Gifts" (expenditures) reflected all charges from my credit card (for Gifts) but did not reflect the return and credit received. For example: Gift Expense CAMERA Purchase $299.95 Gift Expense Camera Return ($299.95) Net "Gift Expense" -0- If I look at the Pie Chart (and underlying details) "Gift Expense" is $299.95 -but- If I go through Banking>Categories>Detail Transactions I see the Charge AND the Credit. This is correcct...

Connection Filtering #5
Hi, I have a question regarding Exchange 2003 Server Connection Filtering configuration. I have added an IP address to allow exchange 2003 server to accept email; but then I started to get spams coming from the IP address. My question is how can I only set up a particular email address from that IP address so that exchange server will allow through but not allow other emails from other addresses from this IP to come in? Please provide me a step by step on the configuration. I am fairly new and not an expert. Many Thanks ...

Menu bar
How can I remove the menu bar from my SDI app after I have started on the program? Peter Simmonds Northampton, UK This was discussed here few days ago: http://tinyurl.com/5r8uv -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Peter Simmonds" <peter@simmonds.worldonline.co.uk> wrote in message news:41f876e9$1_4@mk-nntp-2.news.uk.tiscali.com... > How can I remove the menu bar from my SDI app after I have started on the > program? > > Peter Simmonds > Northampton, UK > > ...

Help needed creating a more compact report
In Microsoft Access 2000, I have a multi-column report that currently prints like this: Abel St 1705 1719 Agate St 200 319 Apt 15 319 Apt 208 405 ... I want to create a report that prints in a much more compact format, like this: Abel St: 1705, 1719 Agate St: 200, 319 (#15, #208), 405 ... What do I need to do? And if this question has been answered before, what would I search on to find the answer? I looked around but couldn't find it. Thanks, Anders Schneiderman -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports...