Date Range in Search Form as 12/30/1899?

[ALewis06]

Using Access 2010, I have a search form that includes both a start date and end date that must be entered each time a user searches. Both of these are unbound text boxes.

For the end date I want it to automatically use the most current date so I entered a default value of =Date().

The data behind the form started being collected on 5/1/2010 so I'd like to enter that as the default value for the Start Date field but it keep showing up as 12/30/1899. How can I fix that?

 

[pr2-eugin]

It is because the Format is set to Short date with Dec/30/1899.. I think it is a US based system.. You can use the Format() to change the Format to dd/mm/yyyy.. the syntax being

Format([DateFieldName],"dd/mm/yyyy")

The above will give you the result 30/12/1899

 

[pr2-eugin]

I might have also misread some bits.. did you want to change the Start Date to be default to 5/1/2010? You can use DMin for that.. something like..

Me.[COLOR=Blue]StartDateFieldName[/COLOR] = DMin("[COLOR=Blue]DateFieldNameInTable[/COLOR]", "[COLOR=Blue]tableName[/COLOR]")

highlighted pieces needs to match as per your need.

 

[boblarson]

Dec 30 1899 is 0 so for some reason it is putting a zero there. Also for queries you must format the criteria to either U.S.date format or a non ambiguous date format like yyyy-mm-dd.

 

[Brianwarnock]

Bob are you saying that dates entered into search forms to drive queries have to be in US format? Is that new in 2010 as I always used UK format no problem. However looking at the op's location I am guessing that he is using US format anyway.

Brian

 

[ALewis06]

Bob,
In the underlying query I had already set the format for both date fields as Short Date yet I am still getting the result of 12/30/1899 in the search form.

 

[boblarson]

Bob are you saying that dates entered into search forms to drive queries have to be in US format? Is that new in 2010 as I always used UK format no problem. However looking at the op's location I am guessing that he is using US format anyway.

Brian

I'm posting from my phone so I don't have the Allen Browne reference but yes to make sure all works as it should, either the U.S date format or a non ambiguous format should be used over dd/mm/yyyy. And that has ALWAYS been the case.

 

[vbaInet]

I'm posting from my phone so I don't have the Allen Browne reference but yes to make sure all works as it should, either the U.S date format or a non ambiguous format should be used over dd/mm/yyyy. And that has ALWAYS been the case.

... here it is:

http://allenbrowne.com/ser-36.html

 

[Brianwarnock]

If I have a query with a criteria of Between date1And date2 with the dates entered into a form that drives the query I have ALWAYS used my default UK format , just as I would have if hard coding in the design grid.

Brian

 

[ALewis06]

I guess I'm not getting it. In the query I have one field called joiningDate. In the related search form I have created 2 unbound text boxes. The box labeled Joining Date Range Start Date is showing up as 12/30/1899 when I want it to automatically pre-populate each time as 5/1/2010. The data is being pulled from a data warehouse via odbc; it comes down in date/time format. What I want is a short date format that looks like mm/dd/yyyy but MORE importantly I want the correct date: 5/1/2010. I tried the DMin function but not being versed in VBA at all, it didn't work for me.

Help

 

[boblarson]

If I have a query with a criteria of Between date1And date2 with the dates entered into a form that drives the query I have ALWAYS used my default UK format , just as I would have if hard coding in the design grid.

Brian

Brian, I'm surprised that it would work for you but even more surprised that, given your long history on the board, you had not heard about this before. There are a ton of posts talking about this very subject.

 

[boblarson]

I guess I'm not getting it. In the query I have one field called joiningDate. In the related search form I have created 2 unbound text boxes. The box labeled Joining Date Range Start Date is showing up as 12/30/1899 when I want it to automatically pre-populate each time as 5/1/2010. The data is being pulled from a data warehouse via odbc; it comes down in date/time format. What I want is a short date format that looks like mm/dd/yyyy but MORE importantly I want the correct date: 5/1/2010. I tried the DMin function but not being versed in VBA at all, it didn't work for me.

Help

In the on load event of your form just put

Me.YourComboBoxNameHere = #5/1/2010#

 

[ALewis06]

And it was that simple! Thanks Bob!!!!

 

[Brianwarnock]

Brian, I'm surprised that it would work for you but even more surprised that, given your long history on the board, you had not heard about this before. There are a ton of posts talking about this very subject.

I've read many posts regarding date difficulties in VBA anSQL but don't remember any to do with forms or the query design grid, probably because it is not a problem. The key quote from the Allene Browne link is

Text boxes bound to Date fields are not a problem, but if the control is unbound (nothing in its Control Source property), how can Access know what data type you intend? If you are in the USA, it will probably guess correctly. If you use another date format, there's a high probability that your date will be interpreted wrongly.

In general, the Format property of the control has to do with how Access displays data and does not control data entry. However, if you set the Format property to "Short Date" or similar, Acce ss is unable format an invalid entry, so only valid dates are accepted. And since Access now knows the data type, the erroneous interpretations of the date also cease.

But what fool would not format his input control

All I know is that I live in the UK and had no problem.

Brian

 

[Brianwarnock]

I believe that VbaInet was trying to give a method that would always return the earliest date in the table in case it changed, a very good programming consideration.

Bria

 

[vbaInet]

I believe that VbaInet was trying to give a method that would always return the earliest date in the table in case it changed, a very good programming consideration.

Bria

That was pr2-eugin Brian

 

[Brianwarnock]

That was pr2-eugin Brian

Oops, apologies to pr2.

Brian

 

[pr2-eugin]

Nah.. No bigge Brian.. Thanks..

BTW: I remember memorizing the Poem by Davies - Leisure.. Great lines

 

[arichins]

I have a weird problem. I have two textboxes in the "medium time" format. And when I use their values as the basis for a search against a SQL Database, the date portion defaults to 12-28-1899 instead of 12-30-1899. In the sql database, the records are 12-30-1899, so it doesn't return results, and I have to manually bump the date up two days prior to executing the query.

 

[boblarson]

I have a weird problem. I have two textboxes in the "medium time" format. And when I use their values as the basis for a search against a SQL Database, the date portion defaults to 12-28-1899 instead of 12-30-1899. In the sql database, the records are 12-30-1899, so it doesn't return results, and I have to manually bump the date up two days prior to executing the query.

That would be because SQL Server and Access do not have the same starting dates. Why do you have dates in your database like that?