I want the user to be able to input "dates and periods" in 3 different formats:
1) dd-mm-yyyy
2) mm-yyyy
3) yyyy
in the SAME textbox.
Then I think I can set up a filter for the dataquery to show me the records.
How can I do that ?
I want the user to be able to input "dates and periods" in 3 different formats:
1) dd-mm-yyyy
2) mm-yyyy
3) yyyy
in the SAME textbox.
Then I think I can set up a filter for the dataquery to show me the records.
How can I do that ?
Combobox so they can select which format? This would alter the format of the text box (perhaps even make the text box enabled too). Then the user can fill in the text box.
Yeah, when it can't be in 1 format-string (and it can't - I have tried !) I, think it would be faster with 3 radiobuttons
thx - I'll try that.
If you changed the mask using the option group_change event to ensure you get a consistent response and made three invisible text boxes you could assign each one as the date month and year and filter from there
I used the radiobuttons and 1 txtbox and it works nicely, BUT ...
When I use the format mm-yyyy and then have a filter like - and I've checked it "StepByStep":
Me.Filter = "HDato >= #01-08-1949# and HDato <= #31-08-1949#"
the resulting set of records INCLUDE a date "14-07-1949" !!!!!!!
Why ????????
Your dates are wrong in filter.
08-01-1949 is first august.
Checkout usage here
Woops ... why doesn't Access check my Windows-setting where I have a date-setting as dd-mm-yyyy ?
Sql always works that way round. No matter where you are.
Grrrrrrrrrr !
I'm so sick and tired of messing around with dates in DANISH and SQL formats - it's driving me crazy !!!!!!
a DANISH date (dd-mm-yyyy) is AUTOMATICALLY converted to SQL-format ... IF IT CAN be done:
01-07-1949 converts to 07-01-1949 but ...
14-07-1949 'converts' to 14-07-1949
What is "best practice" - not Quick&Dirty !
How can I manage that in an organized way ?
Is the date a text field still?
I manage it as a Date-datatype
OK I need clarification
Your user presses a toggle button and types in a date in a field txtdate?
If the date that is entered has format dd-mm-yyyy then the following will convert that text to a date field
You should be able to use this in a filterCode:DateSerial(CInt(Right([txtdate],4)),CInt(Mid([txtdate],4,2)),CInt(Left([txtdate],2)))
Code:me.filter = "Hdato = " & DateSerial(CInt(Right([txtdate],4)),CInt(Mid([txtdate],4,2)),CInt(Left([txtdate],2)))
I haven't seen problems yet when the input mask is dd-mm-yyyy - but maybe there is anyway !
The problem is obviously when using inputmask mm-yyyy and yyyy - here I have to construct an interval of dates in VBA and I can't test while I'm doing the coding because of this automatic converting I told in my earlier posting !
I would like a "Best practice solution" where the Windows-settings is taken into account in the code too.
I beleave this fidling around with "Bits&Bytes" is a dirty solution - eventhough it solves the problem.
EDIT:
If I do this in the immidiate window:
txtDate=#14-07-1949#
? DateSerial(CInt(Right(txtDate,4)),CInt(Mid(txtDate ,4,2)),CInt(Left(txtDate,2)))
14-07-1949
txtDate=#01-07-1949#
? DateSerial(CInt(Right(txtDate,4)),CInt(Mid(txtDate ,4,2)),CInt(Left(txtDate,2)))
07-01-1949
What do I mis ... I see no change at all !