Replies

Tried it but I still get the same error. It runs and then fails. It’s only when I do a group by statement or add any kind of parameter. If I don’t group by any feilds or andd amy parameters it runs and converts the format to a date/time value. I set the properties of the field in the query to date/time value..

SELECT AdminSum.[Admin ID], Count(AdminSum.[CBSR Appointment ID]) AS [CountOfCBSR Appointment ID], AdminSum.[Site ID], CDate(Nz([Appointment Date],#1/1/1900#)) AS AdminDate
FROM AdminSum
WHERE (((AdminSum.[Event Outcome]) Not Like “Candidate No-Show”))
GROUP BY AdminSum.[Admin ID], AdminSum.[Site ID], CDate(Nz([Appointment Date],#1/1/1900#));

This is a question regarding an older post I made. I was wondering is it posible to add a parameter query do the field below. Use a between and statement to search between a date range. Any thoughts? Thanks..

Field: DateValue([Appointment Date])
Criteria: currently shows last 3 months data >=DateAdd(“m”,-3,Date())
New suggested criteria Between [enter date] and [enter date] Cant get to work….

Thanks Hans. I got it now. A new morning breads a clearer mind.

Sorry Hans I mistyped in my posting. I am using YDM not YMD. Can you still adivse? Thanks….

That does work Hans but is there a way to show the number of transaction that were not late? I think thats what I really need. IN this case it would be 33957.
Thanks..

Percentage on time Total Transactions Transactions on time
A1 99.99% A2 33960 A3 = X (in this case 33957)

Hans it got me close butt.. I think I I didn’t exlpain it right..

What I need is?

My query shows the site as 1234A I need all sites like 1234* This showed only 1234A when I ran it. SO we need the last character removed and show all sites like 1234*.. Make sense>? Thanks..

That was it Hans. Thanks so much..

Hans I ran into an issue when I tried to query using the code you gave me. It was working great when the data was like the successful format but I got an invalid procedure call when I tried to query the failed data. Not sure what is going on. Do I have to modify the parameters you gave me? I really appreciate your help. Thanks…

Successful:
1A-2_Class optional vs required
1A-2_Class optional vs required
1A-5_Study Abroad
1A-5_Study Abroad

Failed
07A-5_Power Failure
07A-5_Power Failure
07A-1_Surprise Accomplishment
07A-1_Surprise Accomplishment
07A-2_Second Language Requirement
07A-2_Second Language Requirement
07A-3_Student Art Gallery
07A-3_Student Art Gallery
07A-6-Empathy in Babies
07A-6-Empathy in Babies
07A-4_Entertainment Merchandising
07A-4_Entertainment Merchandising

Hans
Thanks for the help. This works for the most part but when I have any appointment date/time like 06/06/2008 12:00:00 PM it will not show in the query. I also found out that the data in my linked spread sheet is not in date/time format. It’s stored as a text field and I can’t change the data source. Is the a way to modify the date field to show the records with ending in 12:00:00 PM? Thanks….

I got the mid function to working great to pull out the number 5 in the field below. The problem now is I have found that the numeric value is not always in postion 5. They told me it was but I as I looked at some older data I found that this is not the case. The only constant is that the numeric value is always between the – and the _. That being the only constant is there a way I can pull that numeric value out. I really appreciate the help. Thanks..

Using: PromptName: Mid([Prompt name],5,1) I receive the value of 5 in my query….

08V-1_Childhood Activity

Yes Pat it is always a single digit 1 through 6…

I got it figured out. I had to change directories in the .bat file. Something stupid on my part. Thanks again..

Using the Function below to call a batch file which will run an FTP script I wrote. The ftp batch file and script run great when I run the batch file from my shared f drive but when I use the shell command below it looks to run the ftp script from my local C drive my documents folder which is where I stored the ftp batch and script before I moved them to my f drive. Do I somehow have to point to the f drive in the function below or in the script or batch file? I’m clueless. Thanks…

p.s. I’m calling the function using run code in a macro.

Function CallFTPRun()
On Error GoTo CallFTPRun_Err

Dim retval
retval = Shell(“F:FTPFTPFeed.bat”, vbMaximizedFocus)

CallFTPRun_Exit:
Exit Function

CallFTPRun_Err:
MsgBox Error$
Resume CallFTPRun_Exit

End Function

yes. sorry..

I think it group them right but the dates displayed like this 39088. Any thoughts?