Export crosstab queries

[beginner_access]

I have a crosstab queries which uses the date query parameters.

However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?

On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryEXPORT"

Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)

With dlgOpen
  .ButtonName = "Export To"
  .InitialView = msoFileDialogViewLargeIcons
  .InitialFileName = CurrentProject.Path
     If .Show = -1 Then
       'Allow for Root Directory selection: C:\, D:\, etc.
       strExportPath = Replace(.SelectedItems(1) & "\", "\\", "\")
 Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:=conOBJECT_TO_EXPORT, _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
                                                               



       MsgBox "[" & conOBJECT_TO_EXPORT & "] has been Exported to " & strExportPath & _
               conOBJECT_TO_EXPORT & ".xls", vbInformation, "Export Complete"
     End If
End With

'Set the Object Variable to Nothing.
Set dlgOpen = Nothing

DoCmd.Close
Exit_cmdTest_Click:
  Exit Sub

Err_cmdTest_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    Resume Exit_cmdTest_Click

Thanks in advance

 

[MarkK]

It is not this code that is prompting for the said data. You need to show the text of the query.

 

[beginner_access]

It is not this code that is prompting for the said data. You need to show the text of the query.

In my query parameter I have:

[Enter Start Date "dd-mm-yy"] with data type Date/Time
[Enter End Date "dd-mm-yy"] with data type Date/Time

and in my query Date field/column I have:

Between [Enter Start Date "dd-mm-yy"] And [Enter End Date "dd-mm-yy"]

under criteria with Total field set to 'Where'

 

[CJ_London]

Since you have not shown the whole SQL, so no idea how you are using the parameters, you may find this link solves your problem

http://www.access-programmers.co.uk/forums/showthread.php?t=262604&highlight=parameter+asked

 

[beginner_access]

Since you have not shown the whole SQL, so no idea how you are using the parameters, you may find this link solves your problem

http://www.access-programmers.co.uk/forums/showthread.php?t=262604&highlight=parameter+asked

Thanks CJ_London. That didn't really solve my problem. When I hit Export, I am still asked to enter the date parameter twice.

Sorry, here's the code

PARAMETERS [Enter Start Date dd/mm/yy] DateTime, [Enter End Date dd/mm/yy] DateTime;

TRANSFORM Count(tblA.StudyID) AS [# of all Metrics]

SELECT Year([A_Date]) AS [A_Year], Month([A_Date]) AS MonthName, Count(tblA.StudyID) AS [Total Of StudyID], Count(tblA.[FIELD1]) AS [CountOfFIELD1], Count(tblA.[FIELD2]) AS [CountOfFIELD2], Count(tblA.[FIELD3]) AS [CountOfFIELD3], Count(tblA.[FIELD4]) AS [CountOfFIELD4], Count(tblA.[FIELD5]) AS [CountOfFIELD5], Count(tblA.[FIELD6]) AS [CountOfFIELD6], Count(tblA.[FIELD7]) AS [CountOfFIELD7]

FROM tblB INNER JOIN tblA ON tblB.StudyID = tblA.StudyID

WHERE (((tblA. A_Date) Between [Enter Start Date dd/mm/yy] And [Enter End Date dd/mm/yy]))
GROUP BY Year([A_Date]), Month([A_Date])

PIVOT tblA.Activity;

 

[CJ_London]

That didn't really solve my problem

Thanks for posting your code which is as I expected, and the solution I would suggest is exactly as suggested in the link - refer to values on your form, not parameters

Take a look at this link as well

http://www.accessforums.net/reports/report-parameter-dates-prompted-twice-data-chart-37526.html

 

[beginner_access]

Thanks for posting your code which is as I expected, and the solution I would suggest is exactly as suggested in the link - refer to values on your form, not parameters

Take a look at this link as well

http://www.accessforums.net/reports/report-parameter-dates-prompted-twice-data-chart-37526.html

Thanks again CJ_London. I tried the link and for some reason it is still not working. Saying I put invalid brackets []

Between [Forms]![FormName]![StartDate] And [Forms]![FormName]![EndDate]

I also tried using Forms!formname!textboxname and it says the same error message.

I tried both codes under query parameter and also under Criteria

Did I do something wrong? :S

 

[CJ_London]

do you really have a form called 'FormName' or have you just changed it to this so we don't know what your form is really called?

When referring to controls in forms, you should remove the parameters line in your sql

With respect, just posting part of the sql when you have a problem is like saying to the mechanic at the garage - 'my car won't start, I've brought the key'

 

[beginner_access]

do you really have a form called 'FormName' or have you just changed it to this so we don't know what your form is really called?

When referring to controls in forms, you should remove the parameters line in your sql

With respect, just posting part of the sql when you have a problem is like saying to the mechanic at the garage - 'my car won't start, I've brought the key'

Sorry for the late reply as I was out of town.

Here's the sql code for my crosstab

PARAMETERS [[Forms]!Metrics![StartDate]] DateTime, [[Forms]!Metrics![EndDate]] DateTime;
TRANSFORM Count(tblA.StudyID) AS [# of all Metrics]

SELECT Year([A_Date]) AS [A_Year], Month([A_Date]) AS MonthName, Count(tblA.StudyID) AS [Total Of StudyID], Count(tblA.[FIELD1]) AS [CountOfFIELD1], Count(tblA.[FIELD2]) AS [CountOfFIELD2], Count(tblA.[FIELD3]) AS [CountOfFIELD3], Count(tblA.[FIELD4]) AS [CountOfFIELD4], Count(tblA.[FIELD5]) AS [CountOfFIELD5], Count(tblA.[FIELD6]) AS [CountOfFIELD6], Count(tblA.[FIELD7]) AS [CountOfFIELD7]

FROM tblB INNER JOIN tblA ON tblB.StudyID = tblA.StudyID

GROUP BY Year([A_Date]), Month([A_Date])

PIVOT tblA.Activity;

It's actually working now with the code but it display all data and not by whatever date I entered.

 

[beginner_access]

Its working! I added BETWEEN FORMS and Forms to criteria!~

Thank you everyone for your help!