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!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Scripting in VBA 2

Status
Not open for further replies.
jalenben1

jalenben1

Programmer
Jul 22, 2008
154
US
I have a form in Access and I am having a hard time with a VBA script. What I am trying to do is click on a button I created called Generate ECM report. I want to be able to type in a beginning date and a ending date to generate this ECM report. The script I have below is:

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

Dim stDocName As String
Dim stWhere As String

If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] <= " & SQLDate(Me.txtEnding)
ElseIf IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)
ElseIf IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] Between " & SQLDate(Me.txtBeginning) & "And" & SQLDate(Me.txtEnding)
End If
'MsgBox stWhere
Debug.Print "Beginning: " & Me.txtBeginning & "Ending: " & Me.txtEnding & " StWhere: " & stWhere
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_ECM_Report_Click:
Exit Sub

Err_Generate_ECM_Report_Click:
MsgBox Err.Description



End Sub

When I click on the button Generate ECM report I get the following error message:

The action or method requires a Report name argument. Can you tell me ehat that means and can you help me with this script in VBA?
 
Sort by date Sort by votes
I re did the script:

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

Dim stDocName As String
stDocName = "Enterprise Change Management Report"
Dim stWhere As String
Dim strSQL As String
Dim strQueryName As String
strQueryName = "qryECM Query"
strWhere = "1=1 "

If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then stWhere = "[DateCreated] <= " & SQLDate(Me.txtEnding)
If IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)
If IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then stWhere = "[DateCreated] Between" & SQLDate(Me.txtBeginning) & "And" & SQLDate(Me.txtEnding)

End If
'MsgBox stWhere
Debug.Print "Beginning: " & Me.txtBeginning & "Ending: " & Me.txtEnding & " stWhere: " & stWhere
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_ECM_Report_Click:
Exit Sub

Err_Generate_ECM_Report_Click:
MsgBox Err.Description



End Sub

I got the error message End If without block if
 
Upvote 0 Downvote
Hi jalenben1,

I have read your first post very carefully
I then read the error you were receiving.

From what you posted and what the error was returned, it appears that your problem, with the very first code example, was that you never provided a report name to run.

ie: stDocName was left blank or null

I would go back to that version of your code, set stDocName to the name of your report and give it a try.

Hap...

Access Developer [pc] Access based Accounting Solutions - with free source code
Access Consultants forum
 
Upvote 0 Downvote
jalenben1,
I'm not sure why you didn't attempt my code from 25 Jan 11 15:50 with the correction to the strWhere variable name from 26 Jan 11 11:21.

Is there a specific reason why you didn't use this? When someone attempts to help, the least you could do is attempt to implement their suggestion or provide a response that states why you didn't.



Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Hap...

Great advice...I used the following script:

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

Dim stDocName As String
stDocName = " Enterprise Change Management Report"
Dim stWhere As String

If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] <= " & SQLDate(Me.txtEnding)
ElseIf IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)
ElseIf IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] Between " & SQLDate(Me.txtBeginning) & "And" & SQLDate(Me.txtEnding)
End If
'MsgBox stWhere
Debug.Print "Beginning: " & Me.txtBeginning & "Ending: " & Me.txtEnding & " StWhere: " & stWhere
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_ECM_Report_Click:
Exit Sub

Err_Generate_ECM_Report_Click:
MsgBox Err.Description



End Sub

When I click on the command button my report pops up.

I tried to enter a beginning date of 1/1/2011 and a ending date of 1/31/2011 (both dates are text boxes) and I get an error message that says Syntax error (missing operator) in query expression '([DateCreated] Between AND)'. Any suggestions??
 
Upvote 0 Downvote
I would guess that you need a space before and after the word AND

So:
stWhere = "[DateCreated] Between " & SQLDate(Me.txtBeginning) & "And" & SQLDate(Me.txtEnding)

would be:
stWhere = "[DateCreated] Between " & SQLDate(Me.txtBeginning) & " And " & SQLDate(Me.txtEnding)

Hap...

Access Developer [pc] Access based Accounting Solutions - with free source code
Access Consultants forum
 
Upvote 0 Downvote
Hap...

I now have this:

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

Dim stDocName As String
stDocName = " Enterprise Change Management Report"
Dim stWhere As String

If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] <= " & SQLDate(Me.txtEnding)
ElseIf IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)
ElseIf IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] Between " & SQLDate(Me.txtBeginning) & " And " & SQLDate(Me.txtEnding)
End If
'MsgBox stWhere
Debug.Print "Beginning: " & Me.txtBeginning & "Ending: " & Me.txtEnding & " StWhere: " & stWhere
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_ECM_Report_Click:
Exit Sub

Err_Generate_ECM_Report_Click:
MsgBox Err.Description



End Sub

I am still getting the same error message even after I put a space beofre and after AND in the statement you provided.
 
Upvote 0 Downvote
Sometimes when you are selecting using a date field, you need to surround the date value with the # sign.

So:
stWhere = "[DateCreated] Between " & SQLDate(Me.txtBeginning) & " And " & SQLDate(Me.txtEnding)

Would be:
stWhere = "[DateCreated] Between #" & SQLDate(Me.txtBeginning) & "# And #" & SQLDate(Me.txtEnding) &"#"

Give this a try.

Hap...

Access Developer [pc] Access based Accounting Solutions - with free source code
Access Consultants forum
 
Upvote 0 Downvote
Hap...

When I tried your suggestion:

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

Dim stDocName As String
stDocName = " Enterprise Change Management Report"
Dim stWhere As String

If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] <= " & SQLDate(Me.txtEnding)
ElseIf IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)
ElseIf IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] Between #" & SQLDate(Me.txtBeginning) & "# And #" & SQLDate(Me.txtEnding) & "#"
End If
'MsgBox stWhere
Debug.Print "Beginning: " & Me.txtBeginning & "Ending: " & Me.txtEnding & " StWhere: " & stWhere
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_ECM_Report_Click:
Exit Sub

Err_Generate_ECM_Report_Click:
MsgBox Err.Description



End Sub
I get another error messageSyntax error (missing operator) in query expression '([DateCreated]Between ## AND ##)'

Dhookom:

I tried what you gave me but I did not understand your last response.
 
Upvote 0 Downvote
Hi,

I am not sure why you example returns with ## and no date value in between.

How about trying this instead:

stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning) & " And " & "[DateCreated] <= " & SQLDate(Me.txtEnding)

Pehaps the FIlter Property does not like the Between clause.

Hap...

Access Developer [pc] Access based Accounting Solutions - with free source code
Access Consultants forum
 
Upvote 0 Downvote
jalenben1,
This is the code I suggested which you seemed to have ignored and never suggested why you didn't attempt to use any of it.
Code: 
Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click    
    Dim stDocName As String
    stDocName = "Enterprise Change Management Report"
    Dim strWhere As String
    Dim strSQL as String
    Dim strQueryName as String
    strQueryName ="qryECM Query"
    strWhere = "1=1 "
    If IsDate(Me.txtEnding) Then
        'SQLDate() should create the proper format for the pass-through
        strWhere = strWhere & " AND DateCreated  <= " & SQLDate(Me.txtEnding)
    End If
    If IsDate(Me.txtBeginning) Then
        stWhere = strWhere & " AND DateCreated >= " & SQLDate(Me.txtBeginning)
    End If
    'build a sql statement
    strSQL = "SELECT ChangeID, DateCreated, ScheduledStartDate, " & _
        "ScheduledEndDate, ScheduledDuration, Category, Item, " & _
        """Change-Mgmt-Approval-"", Region, Status, ClosureCode, " & _
        "Classification, Description, ""Device-Alias+"", " & _
        "AssignedGroup, AssignedToFullName "
    strSQL = strSQL & "FROM ECM_Change " & _
        "WHERE AssignedGroup='CORP - Huawei Tier II' AND " & strWhere & _
        " ORDER BY ECM_Change.DateCreated DESC "
    'print the SQL statement for troubleshooting
    Debug.Print strSQL
    'change the SQL property of the pass-through
    Currentdb.QueryDefs(strQueryName).SQL = strSQL
    DoCmd.OpenReport stDocName, acViewPreview

Exit_Generate_ECM_Report_Click:
    Exit Sub
Err_Generate_ECM_Report_Click:
    MsgBox Err.Description
End Sub

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
dhookom:

I used that code and it was not working but when I used this code:

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

Dim stDocName As String
stDocName = " Enterprise Change Management Report"
Dim stWhere As String

If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] <= " & SQLDate(Me.txtEnding)
ElseIf IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)
ElseIf IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] Between #" & SQLDate(Me.txtBeginning) & "# And #" & SQLDate(Me.txtEnding) & "#"
End If
'MsgBox stWhere
Debug.Print "Beginning: " & Me.txtBeginning & "Ending: " & Me.txtEnding & " StWhere: " & stWhere
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_ECM_Report_Click:
Exit Sub

Err_Generate_ECM_Report_Click:
MsgBox Err.Description



End Sub

It worked when I clicked on the command button "Generate ECM report" but when I put in dates I got the error:

Syntax error (missing operator) in query expression '([DateCreated]Between ## AND ##)'

Can you give me any suggestions how to make it work whe inputting dates
 
Upvote 0 Downvote
What is the code for the function SQLDate()? Apparently this function is not returning a date value.

Do you have Option Explicit set in your module general declarations?

Have you attempted to set a break point and step through your code? There is an FAQ in the Modules VBA forum that describes how to troubleshoot your code.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
I am not sure of the code for the function SQLDate and I dont have the Option Explicit but I do have this:

Function SQLDate(varDate As Variant) As Variant
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
End If
End If



End Function

Does this help

 
Upvote 0 Downvote
I'm not sure where you got that function but on Allen's web site, the function doesn't have to If lines:
Code: 
Function SQLDate(vDate As Variant) As String
    If IsDate(vDate) Then
        SQLDate = "#" & Format$(vDate, "mm\/dd\/yyyy") & "#"
    End If
End Function
You haven't told us what database server you are using but I expect it would not use "#" as the date delimiter. You may need to create a similar function based on the date format expected by whatever database server you are using. For instance, if you are using MS SQL Server, you could use this function:
Code: 
Function SQLServerDate(vDate As Variant) As String
    If IsDate(vDate) Then
        SQLServerDate = "'" & Format$(vDate, "mm\/dd\/yyyy") & "'"
    End If
End Function


Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
dhookom

It is not on a SQL server per se but I took an QDBC query and created a Pass Through Query in Access and then created a query from the pass thorugh query.


Hap....

your suggestion: stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning) & " And " & "[DateCreated] <= " & SQLDate(Me.txtEnding)

gave me another syntax error ("[DateCreated] >= AND DateCreated]<=
 
Upvote 0 Downvote
What is the connection of your "Pass Through Query in Access"?

I also mentioned "Apparently this function is not returning a date value."
This appears to be true. Until you fix the SQLDate() function, your code will never work.

If you want to write code, you need to understand some best practices such as faq705-7148 and setting Option Explicit and how to compile.






Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Looks like your function SQLDate() can return blanks.

So, give one of these a try:

stWhere = "[DateCreated] Between #" & Me.txtBeginning & "# And #" & Me.txtEnding & "#"

Or This:
stWhere = "[DateCreated] Between " & Me.txtBeginning & " And " & Me.txtEnding

Hap...

Access Developer [pc] Access based Accounting Solutions - with free source code
Access Consultants forum
 
Upvote 0 Downvote
Hap....

That did it!!! Not only am I able to click on the Gereante ECM report button without entering dates I can also enter dates and see the report. Thanks so much. Now I have to scritp it to have the report send automatically. I will try to script it first then let you know what I get.
 
Upvote 0 Downvote
I am looking to send a report automatically I created in from a form in Access. I have a macro that I created and it works fine. I would like to run a macro from a VBA procedure by suing the RunMacro method of the DoCmd Object. So far I have:

DoCmd.SendObject _

acSendReport, _

"Enterprise Change Management Report", _

acFormatRTF, _

"Todwayne.harris@cox.com", _

"Torandall.dameron@cox.com, _

"Tomark.rowe@huawei.com, _

"Todameron@huawei.com, _



"Subject", "Enterprise Change Management Report _

"Message", "Attached is the current ECM report 2011_

False


Is this the right command?
 
Upvote 0 Downvote
Hi jalenben1,

It seems like with the help of myself and others, you have now solved the first problem that you asked for help.

At this point, it is time to thank those that helped you and then move on.

If you have a new problem, you should now start a new Post and ask about your next/new problem in the new Post.

And, remember, you can always try and figure out your problems yourself. The best way to learn is to try solving your own problems. Only when you can not solve a problem by yourself, should you then ask for help. Just my 2 cents.

Good Luck,
Hap...

Access Developer [pc] Access based Accounting Solutions - with free source code
Access Consultants forum
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
561
Kennelbloke
Kennelbloke
PrimoSteve
  • Locked
  • Question
Fill in 2nd Textbox2 from Textbox1
Replies
3
Views
443
PrimoSteve
PrimoSteve
Xavierparkash
  • Locked
  • Question
MS Access Slow Search in Linked Database
Replies
0
Views
238
Xavierparkash
Xavierparkash
caykamanj1966
  • Locked
  • Question
Duplicate Records in DataSheet View
Replies
5
Views
489
Andrzejek
Andrzejek
Modex
  • Locked
  • Question
How to Close Desktop Application Windows using VBA
Replies
3
Views
252
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top