Date convert
Hi Experts,
I have a question about date convert. Currently I have a field that is a date/time and default as short date. now we need to change to just month/year in stead mm/dd/yyyy. I do not want to change anything in th SQL about this data field because we have over hundred thousand records in the table. what I want to do now is put two unbound field in the form, when enter mm & yyyy on these two unbound fields, so I can convert this two unbound field to the data field in the back ground, but I'm having trouble to put the first day of the month to put into the date field.
the Current date field is name "SUTEMDate". I have one unbound field name "SUMM" and another unbound field name "SUYY".
here is my coding on both unbound field after update:
If Not IsNull(SUMM) then
SUTEMDate = SUMM
end if
but it seems not working. What I want to do is if I enter 07 in the SUMM field and 2013 in the SUYY field then the SUTEMDate should show 07/01/2013.
Thanks,
I have a question about date convert. Currently I have a field that is a date/time and default as short date. now we need to change to just month/year in stead mm/dd/yyyy. I do not want to change anything in th SQL about this data field because we have over hundred thousand records in the table. what I want to do now is put two unbound field in the form, when enter mm & yyyy on these two unbound fields, so I can convert this two unbound field to the data field in the back ground, but I'm having trouble to put the first day of the month to put into the date field.
the Current date field is name "SUTEMDate". I have one unbound field name "SUMM" and another unbound field name "SUYY".
here is my coding on both unbound field after update:
If Not IsNull(SUMM) then
SUTEMDate = SUMM
end if
but it seems not working. What I want to do is if I enter 07 in the SUMM field and 2013 in the SUYY field then the SUTEMDate should show 07/01/2013.
Thanks,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
First, always handle dates as date/time, not strings, not numbers. No exceptions.
Second, you don't need to and should not change anything in your database.
A date in a field of data type Date is one value only. The format is only used for display.
If you have to have two input fields, use the function intended for assembling a date.
Thus:
If Not IsNull(Me!SUYY + Me!SUMM) then
Me!SUTEMDate = DateSerial(Me!SUYY, Me!SUMM, 1)
end if
/gustav
Second, you don't need to and should not change anything in your database.
A date in a field of data type Date is one value only. The format is only used for display.
If you have to have two input fields, use the function intended for assembling a date.
Thus:
If Not IsNull(Me!SUYY + Me!SUMM) then
Me!SUTEMDate = DateSerial(Me!SUYY, Me!SUMM, 1)
end if
/gustav
ASKER
both of billdenver & cauctus_data are works, but is there any way when user enter 07 in SUMM and 2013 in SUYY, the SUTEMdate will show 07/xx/2013? or the day has to be number?
thanks
thanks
You can build your own format property for the textbox:
mm/\x\x/yyyy
or create an expression for display:
YearMonth: Format([SUTEMdate], "mm/\x\x/yyyy")
/gustav
mm/\x\x/yyyy
or create an expression for display:
YearMonth: Format([SUTEMdate], "mm/\x\x/yyyy")
/gustav
Hi,
You might also try the following in the format property of the text box:
"mm-yyyy"
There is really no need to show the day if you're not using it.
Regards,
Bill
You might also try the following in the format property of the text box:
"mm-yyyy"
There is really no need to show the day if you're not using it.
Regards,
Bill
ASKER
question for cactus_data, how do I apply thie format in the coding?
Hi,
Put "mm/\x\x/yyyy" in the format property of the text box.
Regards,
Bill
Put "mm/\x\x/yyyy" in the format property of the text box.
Regards,
Bill
ASKER
Is possible a data field can store as mm/letter/yyyy or has to be mm/dd/yyyy?
You can store it that way but not as date so date math will not work. If you want to keep track of dates, even month and year only then use a formatted date field. cactus_data is correct - above.
Bill
Bill
No. It is neither. There is no format only a value.
Format is for display only. If not format string is specified, that of your Windows settings is used.
/gustav
Format is for display only. If not format string is specified, that of your Windows settings is used.
/gustav
ASKER
YearMonth: Format([SUTEMdate], "mm/\x\x/yyyy") works in query, but when I tried to pu in form current, it seems not pick it up.
here is what I do:
I enter "07" in SUMM, "2013" in SUYY, either of your suggest works, it really shows 07/01/2013 in the table under the SUTEMDate field. so this part is fine.
If Not IsNull(SUMM) AND NOT IsNull(SUYY) then
SUTEMDate = cvdate(cstr(SUMM) & "/01/" & cstr(SUYY))
end if
or
If Not IsNull(Me!SUYY + Me!SUMM) then
Me!SUTEMDate = DateSerial(Me!SUYY, Me!SUMM, 1)
end if
so I do have a the existing SUTemDate field in the form, I need to be invisible this field instead have another unbound field name "HoldSU" to show "07/xx/2013" instead to show the two unbound field whiach are SUMM & SUYY. so I use : (I put this in form Current)
dim HoldSu as Variant
If Not IsNull(Me.SUTEMDate) Then
Me!SUMM.Visible = False
Me!SUYY.Visible = False
HoldSu = Format([SUTEMDate], "mm/\x\x/yyyy")
Else
Me!SUMM.Visible = True
Me!SUYY.Visible = True
End If
but it gave me a blank on the HoldSu field
here is what I do:
I enter "07" in SUMM, "2013" in SUYY, either of your suggest works, it really shows 07/01/2013 in the table under the SUTEMDate field. so this part is fine.
If Not IsNull(SUMM) AND NOT IsNull(SUYY) then
SUTEMDate = cvdate(cstr(SUMM) & "/01/" & cstr(SUYY))
end if
or
If Not IsNull(Me!SUYY + Me!SUMM) then
Me!SUTEMDate = DateSerial(Me!SUYY, Me!SUMM, 1)
end if
so I do have a the existing SUTemDate field in the form, I need to be invisible this field instead have another unbound field name "HoldSU" to show "07/xx/2013" instead to show the two unbound field whiach are SUMM & SUYY. so I use : (I put this in form Current)
dim HoldSu as Variant
If Not IsNull(Me.SUTEMDate) Then
Me!SUMM.Visible = False
Me!SUYY.Visible = False
HoldSu = Format([SUTEMDate], "mm/\x\x/yyyy")
Else
Me!SUMM.Visible = True
Me!SUYY.Visible = True
End If
but it gave me a blank on the HoldSu field
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry to keep asking the question, how to show the date if I just enter the moth & year. for example, if a child's birthday is 6/30/2000, when I enter "06" in the SUMM and "2018" in the SUYY, is anyway I can show on the SUTEMdate to "6/30/2018" instead "6/1/2018"? show what ever the date of Birth instead first day of the month? one more example 5/28/1997, enter "05" "2012" will show "05/28/2012"
Where will you enter the 30?
Bill
Bill
ASKER
the 30 is the store in the another table and I will use DLookup to get the birthday
This is confusing, but it could be:
Dim datDOB As Date
Dim varDOB As Variant
varDOB = DLookUp(....)
If Not IsNull(varDate) Then
datDOB = varDOB
Me!SUTEMDate = DateAdd("yyyy", DateDiff("yyyy", datDOB, Date), datDOB)
End If
/gustav
Dim datDOB As Date
Dim varDOB As Variant
varDOB = DLookUp(....)
If Not IsNull(varDate) Then
datDOB = varDOB
Me!SUTEMDate = DateAdd("yyyy", DateDiff("yyyy", datDOB, Date), datDOB)
End If
/gustav
ASKER
Thanks so much both of your time and patience also a great help. very very appricated!!!!!
You are welcome!
/gustav
/gustav
If Not IsNull(SUMM) AND NOT IsNull(SUYY) then
SUTEMDate = cvdate(cstr(SUMM) & "/01/" & cstr(SUYY))
end if
Regards,
Bill