Rearranging input data from an unbound text box

[Guirg]

Hi all!

My last little bug and no more annyoing help me messages!! !!! Is is possible to have someone input data into a text box and then in a subroutine grab it rearrange it and put it back together?

what i want it to do is:
DD/MM/YYYY - split it into DD, /, MM and /YYYY then swap the 1st and 3rd parts around to MM,/,DD,/YYYY? was thinking strings and splitting but ive never attempted somethign like this - any ideas or suggestions?

Cheers

Tim

 

[khawar]

are you storing dates as text

 

[Guirg]

the tables are sorted as dates in the DD/MM/YYYY format but when i applied a filter the filter applies it in MM/DD/YYYY format so i wanted to do was have the type the data in the format DD/MM/YYYY and then re-arrange and then filter

' Check for min date
If Me.txtdatemin > "" Then
'throw the rearrage in here
varWhere = varWhere & "[DateRun] >= #" & Me.txtdatemin & "# And "
End If

 

[Galaxiom]

There are many Access and VBA functions available to parse and rearrange dates.

However it appears that you are building a query for your filter.
Instead of entering the date as a string why not read it directly from the control where the date format is intrinsicly understood.

varWhere & "[DateRun] >= Forms!Formname.txtdatemin And "

 

[Guirg]

Just tried it and got an 'enter value parameter' box :S

 

[Galaxiom]

Is the text box on a subform?
If so use the appropriate syntax as described here:
http://www.mvps.org/access/forms/frm0031.htm

Except that in a query you cannot use the Relative addressing of controls (Me!etc)
You must enter it as Forms!FormName.ControlName

 

[gemma-the-husky]

a date should be a date

set your textbox as (say) a short date format, usually bound to a field in date format

you then enter eg 12/6/09

this date will obey your regional settings

if you are in UK this will be 12th June
in US it will be Dec 6th (hard to think like an american)

you can get date pickers also - displays a simple calendar you can easily move through to pick whatever date you want.

 

[Guirg]

I managed to find this on the web somewhere and just applied it and created a new text box as txtdatemin1 hid in and it seems to work fine
If Me.txtdatemin > "" Then
Dim strSeparator
Dim strTheMonth
Dim strTheYear
Dim strTheDay
Dim strFinalDate
strSeparator = "/"
strTheMonth = Month(txtdatemin)
strTheYear = Year(txtdatemin)
strTheDay = Day(txtdatemin)
strFinalDate = strTheMonth & strSeparator & _
strTheDay & strSeparator & _
strTheYear
txtdatemin1 = strFinalDate
varWhere = varWhere & "[DateRun] >= #" & Me.txtdatemin1 & "# And "
End If