Solved How to make date from unbound form in access be recognized in any runtime environment

[nector]

I have just encountered a problem in access runtime environment; I have created an unbound access form specifically to aid users to select dates required for a parameterized report, this form work very well in in full version of Access and is able to recognize the dates but fail to recognize the same dates in Access runtime environment.

Is there a way to sort out this?

I have chosen all the date controls to short date see the picture but still nothing works any idea to sort out this.

 

[MarkK]

but fail to recognize the same dates

You need to describe the symptoms of this failure. Just to say it fails is not enough info to troubleshoot.

 

[sonic8]

Is there a way to sort out this?

Please show the code that fails.

The most common cause of such problems is when dates are passed on as text and not in Date/Time typed variables.

 

[nector]

Here is what happens when you enter the date using a date picker, see the picture below: this only happen in Access runtime NOT full version

 

[sonic8]

Is there a Validation Rule defined for the control in the form?
Does this Validation Rule expect the date to be in a specific format?

 

[MarkK]

Is the Access runtime running on a different machine? Does the different machine have different regional settings for date formats? Can you enter a date directly that works? On the machine that works, rename the working file extension to .accdr. Run it. It will open in runtime mode. Does it work?

 

[nector]

Is there a Validation Rule defined for the control in the form?
Does this Validation Rule expect the date to be in a specific format?

No validation rule at all

 

[nector]

Is the Access runtime running on a different machine? Does the different machine have different regional settings for date formats? Can you enter a date directly that works? On the machine that works, rename the working file extension to .accdr. Run it. It will open in runtime mode. Does it work?

To answer your question, it seems like you to say I use this method below which also works in full version Access I do not in runtime:

Private Sub txtPosCashEndDate_AfterUpdate()
Me.txtPosCashEndDate = Format((Me.txtPosCashEndDate), "YYYY\/MM\/DD")
End Sub

Private Sub txtPosCashStartDate_AfterUpdate()
Me.txtPosCashStartDate = Format((Me.txtPosCashStartDate), "YYYY\/MM\/DD")
End Sub

 

[nector]

Here is what happens when you enter the date using a date picker, see the picture below: this only happen in Access runtime NOT full version

View attachment 121170

See the new code I'm have just put

Private Sub txtPosCashEndDate_AfterUpdate()
Me.txtPosCashEndDate = Format((Me.txtPosCashEndDate), "YYYY\/MM\/DD")
End Sub

Private Sub txtPosCashStartDate_AfterUpdate()
Me.txtPosCashStartDate = Format((Me.txtPosCashStartDate), "YYYY\/MM\/DD")
End Sub

This works well in full version , but runtime I will try it tomoroww

 

[sonic8]

No validation rule at all

Well, you created an indirect validation rule with your VBA code in combination with the Short Date format of the controls...

Me.txtPosCashEndDate = Format((Me.txtPosCashEndDate), "YYYY\/MM\/DD")

Here you force YYYY/MM/DD as date format, which might not be a valid date format on the target (runtime) computer. - And, actually, I don't think YYYY/MM/DD is a valid date format anywhere. It might just be "tolerated" on your main computer.

Either use this instead:

Me.txtPosCashEndDate = Format((Me.txtPosCashEndDate), "Short Date")

Or remove the shown VBA code altogether. I cannot see any meaningful purpose it could have.

 

[CJ_London]

the code converts the date, a numeric datatype to a string. Not tested by I suspect the datepicker doesn't like strings - your error message says it all. And hopefully these controls are unbound otherwise you are mixing datatypes. But why need to code anyway? why not just set the format property of the control to yyyy/mm/dd?

If it is to be used as criteria in a query, do your string formatting there. Or have a hidden control with a controlsource of =Format(txtPosCashEndDate, "YYYY\/MM\/DD")

 

[nector]

Well, you created an indirect validation rule with your VBA code in combination with the Short Date format of the controls...


Here you force YYYY/MM/DD as date format, which might not be a valid date format on the target (runtime) computer. - And, actually, I don't think YYYY/MM/DD is a valid date format anywhere. It might just be "tolerated" on your main computer.

Either use this instead:

Me.txtPosCashEndDate = Format((Me.txtPosCashEndDate), "Short Date")

Or remove the shown VBA code altogether. I cannot see any meaningful purpose it could have.

The Short Date format on the format control fail to work hence forcing the unambiguous ISO date .

 

[nector]

the code converts the date, a numeric datatype to a string. Not tested by I suspect the datepicker doesn't like strings - your error message says it all. And hopefully these controls are unbound otherwise you are mixing datatypes. But why need to code anyway? why not just set the format property of the control to yyyy/mm/dd?

If it is to be used as criteria in a query, do your string formatting there. Or have a hidden control with a controlsource of =Format(txtPosCashEndDate, "YYYY\/MM\/DD")

Any at query level we always use Format((Date),"YYYY\/MM\/DD") for years over 10 years we do not have any issue I will try it this afternoon or tomorrow and post the results

 

[CJ_London]

personally I use the standard ISO format of yyyy-mm-dd in queries to avoid any possible confusion.

Also, some rdbms/systems will distinguish between M and m and D and d (uppercase are long dates and the lower case short date) although should not an issue for Access which is not case sensitive

 

[sonic8]

The Short Date format on the format control fail to work hence forcing the unambiguous ISO date .

What means "fail to work" exactly? Does it raise an error or does it appear to do nothing? - The latter would be expected when there is a valid date in the selected format as per Windows Regional Settings already.

Also, please note: YYYY/MM/DD is not an ISO date format. The ISO date format either uses hyphens as separator (recommended!) or no separator at all.

It is generally a bad idea to format dates in the UI with a hardcoded date format. This will either do nothing, if the format is compatible to the currently configured date format (Windows Regional Settings), or it will cause an error if they are not compatible.
Of course, you can use any date format you want, if you just display dates in the UI as read-only.

 

[arnelgp]

just add the Format YYYY/MM/DD to the Format property of those two textbox in Design view,
no need any code on afterUpdate event.

 

[nector]

Ok after checking their computers we discovered that there is actually access 2019 full version BUT not activated, and so we told them to immediately to find the product key and activate it and then remove runtime 2013. I'm sure this will work now.