Link to home
Create AccountLog in
Avatar of Doc1505
Doc1505

asked on

Convert Date from a form to a crosstab query

I have a form that has a text field [date] that is formatted to mm/yyyy
I have a command button that prints a form JIB

Private Sub Command123_Click()
On Error GoTo Err_Command123_Click
SetLegalSize ("JIB")
Dim strReportName As String
    Dim strCriteria As String
       strReportName = "JIB"
    strCriteria = "[Well ID]='" & Me![Well ID Main] & "'"
    DoCmd.OpenReport strReportName, acPreview, , strCriteria
Exit_Command123_Click:
    Exit Sub

Err_Command123_Click:
    MsgBox Err.Description
    Resume Exit_Command123_Click
End Sub

The report uses a crosstab query that I have transferring the date to it like
[Forms]![JIBCharges]![Date]

The problem I am having is that it will not convert the date to YYYY only, this causes the crosstab query to have more records, because it lists jan, feb, mar, etc,  not just the year combine.  
I have tried Forms!JIBCharges!Date.Format = "yyyy"
I can do this with the crosstab prompting me for the YYYY but would rather have it not prompt me.

Expr2: Format([Date],"yyyy")
Group by
Row Heading

Format([Date],"yyyy")
Where
[Enter Year YYYY]

Expr1: Format([Date],"mmm")
Group by
Column Heading
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Doc1505
Doc1505

ASKER

That worked thanks alot
Great!

;-)