I've written visual basic code that supplies date data from variables and
the way I did it was to supply a SQL statement to access. Below is the code.
There should be no problem transposing this into a VEE formula object.
' Create permanent QueryDef.
With ManDb2
Set qdfNew = .CreateQueryDef("RepairData", _
"SELECT Failures.ProcStep, Failures.UUTType, Failures.QTY,
Failures.CauseCode, Failures.RefDes, Failures.PN, Failures.Date" _
& " FROM Failures" _
& " WHERE Failures.Date BETWEEN #" & StartDate & "# And #" & EndDate &
"#" _
& " ORDER BY Failures.ProcStep, Failures.UUTType DESC , Failures.QTY
DESC , Failures.CauseCode;")
End With
If StartDate = EndDate Then
ReportTitleString = Format(StartDate, "dddd, mmm d yyyy")
Else
ReportTitleString = "" + "=" + StartDate + " to " + EndDate + ""
Debug.Print ReportTitleString
End If
'open access to print report
ConnectAccess
With Access
.OpenCurrentDatabase DbPath & "" & Dbn
.DoCmd.OpenReport "Failure Repairs Report", acViewPreview
.Reports![Failure Repairs Report]!lblDates.Caption = ReportTitleString
.DoCmd.PrintOut , acPages, CommonDialog1.FromPage, CommonDialog1.ToPage,
, CommonDialog1.Copies
.DoCmd.Close acReport, "Failure Repairs Report", acSaveNo
End With
'delete stuff and quit access
ManDb2.QueryDefs.Delete "RepairData"
Access.CloseCurrentDatabase
Access.Quit acExit
Set Access = Nothing 'release access object
Exit Sub 'do not trample over error handler
PrintError:
If Err = 32755 Then 'error generated by commondialog cancel button
Err.Clear
Exit Sub
Else
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End If
Err.Clear
End Sub
-Andrew Kronquist
----- Original Message -----
From: Martin,Roger <rmartin@granville.com>
To: HP VEE Reflector (E-mail) <hpvxd_xc@hpislsup.lvld.hp.com>
Sent: Wednesday, August 18, 1999 6:12 PM
Subject: VRF: Access question
> Hi all,
>
> First, thanks to everyone's help on my previous questions. They really
> helped.
>
> Has anyone written code to print an Access 97 report from HP VEE 5.01 on
NT
> 4.0?
>
> Specifically, I'm having trouble generating a report that is based on a
> query. The query requires the operator to enter a batch number, which
> forces the query to return only those records that match the requested
batch
> number. What I would like to do is use HP VEE to supply that batch number
> to the query so the operator doesn't have to type it in.
>
> Any thoughts?
>
> Roger Martin
> Manufacturing Engineer
> email: rmartin@granville.com
> phone: 303-998-4045
> fax: 303-443-2546
> HELIX TECHNOLOGY CORP.
> Granville-Phillips
> Boulder, CO USA
the way I did it was to supply a SQL statement to access. Below is the code.
There should be no problem transposing this into a VEE formula object.
' Create permanent QueryDef.
With ManDb2
Set qdfNew = .CreateQueryDef("RepairData", _
"SELECT Failures.ProcStep, Failures.UUTType, Failures.QTY,
Failures.CauseCode, Failures.RefDes, Failures.PN, Failures.Date" _
& " FROM Failures" _
& " WHERE Failures.Date BETWEEN #" & StartDate & "# And #" & EndDate &
"#" _
& " ORDER BY Failures.ProcStep, Failures.UUTType DESC , Failures.QTY
DESC , Failures.CauseCode;")
End With
If StartDate = EndDate Then
ReportTitleString = Format(StartDate, "dddd, mmm d yyyy")
Else
ReportTitleString = "" + "=" + StartDate + " to " + EndDate + ""
Debug.Print ReportTitleString
End If
'open access to print report
ConnectAccess
With Access
.OpenCurrentDatabase DbPath & "" & Dbn
.DoCmd.OpenReport "Failure Repairs Report", acViewPreview
.Reports![Failure Repairs Report]!lblDates.Caption = ReportTitleString
.DoCmd.PrintOut , acPages, CommonDialog1.FromPage, CommonDialog1.ToPage,
, CommonDialog1.Copies
.DoCmd.Close acReport, "Failure Repairs Report", acSaveNo
End With
'delete stuff and quit access
ManDb2.QueryDefs.Delete "RepairData"
Access.CloseCurrentDatabase
Access.Quit acExit
Set Access = Nothing 'release access object
Exit Sub 'do not trample over error handler
PrintError:
If Err = 32755 Then 'error generated by commondialog cancel button
Err.Clear
Exit Sub
Else
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End If
Err.Clear
End Sub
-Andrew Kronquist
----- Original Message -----
From: Martin,Roger <rmartin@granville.com>
To: HP VEE Reflector (E-mail) <hpvxd_xc@hpislsup.lvld.hp.com>
Sent: Wednesday, August 18, 1999 6:12 PM
Subject: VRF: Access question
> Hi all,
>
> First, thanks to everyone's help on my previous questions. They really
> helped.
>
> Has anyone written code to print an Access 97 report from HP VEE 5.01 on
NT
> 4.0?
>
> Specifically, I'm having trouble generating a report that is based on a
> query. The query requires the operator to enter a batch number, which
> forces the query to return only those records that match the requested
batch
> number. What I would like to do is use HP VEE to supply that batch number
> to the query so the operator doesn't have to type it in.
>
> Any thoughts?
>
> Roger Martin
> Manufacturing Engineer
> email: rmartin@granville.com
> phone: 303-998-4045
> fax: 303-443-2546
> HELIX TECHNOLOGY CORP.
> Granville-Phillips
> Boulder, CO USA
First, thanks to everyone's help on my previous questions. They really
helped.
Has anyone written code to print an Access 97 report from HP VEE 5.01 on NT
4.0?
Specifically, I'm having trouble generating a report that is based on a
query. The query requires the operator to enter a batch number, which
forces the query to return only those records that match the requested batch
number. What I would like to do is use HP VEE to supply that batch number
to the query so the operator doesn't have to type it in.
Any thoughts?
Roger Martin
Manufacturing Engineer
email: rmartin@granville.com
phone: 303-998-4045
fax: 303-443-2546
HELIX TECHNOLOGY CORP.
Granville-Phillips
Boulder, CO USA