date sorting in query?

[CHAOSinACT]

i've hit a bit of frustration here, thought i would throw it to the group.

i have a form with some dates on it, you hit the button and the next form interprets the dates and filters results on a between date to date basis.

I'm international btw, so it gets a bit confusing.

once you have filtered results and entered the form there are some buttons linked to query datasheets so you can see how we got the result. the filtering by daterange is a new function, wasn't plannned in the beginning...

I've gotten queries to sort by jobnumber by chucking a returning function in the query before, so i assumed chucking FixDate() in the ActivityDate field of the QBE would work (function below)

i know i need to put International dates in the QBE and US in SQL, i've tried both ways here. oddly if i copy and paste the Debug.print statement (if its in international format) it works. annoying. Ideas? (btw i know the code below is a bit long winded but been trying everything to see if it helps) Thanks in advance!



Public Function FixDate() As String

Dim intDayStart As Integer
Dim intDayEnd As Integer
Dim intMonthStart As Integer
Dim intMonthEnd As Integer
Dim intYearStart As Integer
Dim intYearEnd As Integer

Dim strStart As String
Dim strEnd As String

intDayStart = Day([Forms]!frmCivilMinorJobsMYOB_Overview!txtExpDateToDateStart)
intMonthStart = Month([Forms]!frmCivilMinorJobsMYOB_Overview!txtExpDateToDateStart)
intYearStart = Year([Forms]!frmCivilMinorJobsMYOB_Overview!txtExpDateToDateStart)

intDayEnd = Day([Forms]!frmCivilMinorJobsMYOB_Overview!txtExpDateToDateEnd)
intMonthEnd = Month([Forms]!frmCivilMinorJobsMYOB_Overview!txtExpDateToDateEnd)
intYearEnd = Year([Forms]!frmCivilMinorJobsMYOB_Overview!txtExpDateToDateEnd)

strStart = intMonthStart & "/" & intDayStart & "/" & intYearStart
strEnd = intMonthEnd & "/" & intDayEnd & "/" & intYearEnd
Debug.Print "Between #" & strStart & "# And #" & strEnd & "#"
FixDate = "Between #" & strStart & "# And #" & strEnd & "#"


End Function

 

[CHAOSinACT]

got it. never worked out why it didn't work but this:

Between [Forms]![frmCivilMinorJobsMYOB_Overview]![txtExpDateToDateStart] And [Forms]![frmCivilMinorJobsMYOB_Overview]![txtExpDateToDateEnd]

in the QBE worked