not show today

[azhar2006]

Hello guys
I try to show in the history of the field is distributed on 3 fields
Used the query but I can not show today
For example, in the history of the field is 10/31/2008
Please help
SELECT Right([Submit_Date],4) AS Submit_Year, Left([Submit_Date],InStr([Submit_Date],"/")-1) AS Submit_Month, MyTable.State, MyTable.Submit_Date
FROM MyTable
GROUP BY Right([Submit_Date],4), Left([Submit_Date],InStr([Submit_Date],"/")-1), MyTable.State, MyTable.Submit_Date
ORDER BY Right([Submit_Date],4), Left([Submit_Date],InStr([Submit_Date],"/")-1);

[orange]

Is the field a Date datatype? If so, use Year (Submit_Date) also Month() and Day()

I'm not really understanding your question. An example may help, if I've guessed incorrectly.

[azhar2006]

Thank orange
I reached for half the solution, but I'm having trouble with the colon (/) How do I remove these
This is the expression that you used to date field
Days: Mid(Trim([Submit_Date]);3;3)

[orange]

Try
Mid(Submit_Date,4,2)

I don't know where your data originated, but use Date/Time data type for Dates. It will save you may hours of syntax headaches.

[JoeM]

You didn't answer Orange's question. If your Submit_Date field is a Date datatype, there is no need to use String functions like LEFT, RIGHT, and INSTR. You can simply use the YEAR, MONTH, and DAY functions. Then the issue with the slashes (/) is non-existent.

If you Submit_Date field is String/Text datatype you can still use the YEAR, MONTH, and DAY functions on it. Just convert the field to a date first using the CDATE function, like this:

MyYearCalc: YEAR(CDATE([Submit_Date]))
MyMonthCalc: MONTH(CDATE([Submit_Date]))
MyDayCalc: DAY(CDATE([Submit_Date]))

[azhar2006]

Thank orange, thanks JoeM
This is what you're looking for
MyYearCalc: YEAR(CDATE([Submit_Date]))
MyMonthCalc: MONTH(CDATE([Submit_Date]))
MyDayCalc: DAY(CDATE([Submit_Date]))