Count Query based on Criteria

[boblarson]

What's with all of the ^ signs?

 

[txgeekgirl]

This line - as a text string - gets batched to Medicare and the Medicare system expects those carrots. So we wrote them into the Line.

 

[boblarson]

This line - as a text string - gets batched to Medicare and the Medicare system expects those carrots. So we wrote them into the Line.

Okay, then you need to assign an alias to that and you were missing a space after = DTSQtrSum.Staff
And after = MyQtr))"

Final_Line = "SELECT '02^' & [Quarter] & '^' & [Year] & '^MHMR^170^' & [SSN] & '^'" & _
"Trim([JOB #]) & '^' & [Wk1_Start_Date] & '^' & IIf(IsNull([Wk5_Stop_Date]),[Wk4_Stop_Date],[Wk5_Stop_Date])" & _
"'^' & [SumOfSumOfUnits] & '^' & [SumOfSumOfUnits] & '^I^N^' &

 & '^'" & _
"[SumOfUnits] [COLOR="Red"]As MyFieldName "[/COLOR] & _
"FROM [Project Parameters], (Users INNER JOIN DTSCodeCount ON Users.[User ID] = DTSCodeCount.Staff) INNER JOIN DTSQtrSum ON Users.[User ID] = DTSQtrSum.Staff " & _
"WHERE ((([Project Parameters].Quarter) = MyQtr)) " & _
"ORDER BY Users.[SSN ], DTSCodeCount.Code;"

DoCmd.RunSQL Final_Line

 

[txgeekgirl]

Hi Bob - Thanks for the help last night. I did try to run the code again and got the same error (attached). Since the query runs from the DoCmd, I can run it from there. It would just mean the EU will have to input the Quarter.

Any suggestions on exporting the make table to a Text file from the VBA after that final line runs?

 

[txgeekgirl]

Hi Bob - I wrote a macro and it's great. Thanks for all your help with this!!! I really appreciate it!!!