Date formating (1 Viewer)

[MoxieCraz]

I have an unbound text box on my Access 2010 Form that has the default value of: =DateSerial(Year(Now()),Month(Now()),1)-1
This gives me the first day of the current month minus one...basically giving me the last day of the previous month. It is formatted as mm/dd/yyy. This shows up correctly on the form; however, when I enter the criteria in a query to pull the data in this text box from my form...it drops the leading zero from the month and day and does not recognize the formating. How do I create a work around?

For Example: on the form it may read 03/31/2011, but in the query it is looking for 3/31/2011.

So if I do a =Left([MyDateTextBox],2) in my query it shows 3/ rather than 03.

Thank you for your help.

 

[boblarson]

If the leading zero is stored in your table then you have it set as a TEXT field and not as a date/time field (which is what it SHOULD be).

 

[boblarson]

In the query you can use Format([DateFieldOrFormAndControlReference], "mm/dd/yyyy")

 

[MoxieCraz]

This is what I needed!!!! Thank you so much!!!!

 

[MoxieCraz]

the next peice I need assistance on....

I have daily data in a table. I want my query to only give me the last day of every month. So all other days (for this particular query) is not needed. How do I write the criteria in the query to only pull the last day of each month?

 

[boblarson]

If you just want the last day of the previous month for example, you can set the criteria to be:

DateSerial(Year(Date()), Month(Date()), 0)

 

[MoxieCraz]

yes, but what if I want all of the last day of the months from a table that contains multiple months by day records?

 

[boblarson]

Ouch, I'm going to have to ponder that a bit. That isn't particularly easy (at least it doesn't seem to be to me at them moment).

 

[boblarson]

Okay, I just got a flash of inspiration. What I would do is create a table with 12 entries. Make an entry for each month's last day so like this:

tblMonthsLastDays
MonthNum - Integer
LastDay - Integer


And enter in the month number and the day number for the month's last day:

MonthNum        LastDay
  1                     31
  2                     28
  2                     29
  3                     31
  4                     30
  5                     31
  6                     30
  7                     31
  8                     31
  9                     30
10                     31
11                     30
12                     31

And then create a query which pulls that together with the current year:

SELECT DateSerial(Year(Date()),[MonthNum],[LastDay]) AS CurMonthsLastDays
FROM tblMonthsLastDays
ORDER BY DateSerial(Year(Date()),[MonthNum],[LastDay]);

And save that query as qryMonthsLastDays

And then you can create your own query for pulling the applicable information by linking this table on the date field you have and there you go. It should work wonders (hopefully you don't need any other year except the current one).

 

[MoxieCraz]

Thanks Bob! I was thinking that too as I was sitting here, but was hoping there was something different. I will give this a whirl! Thanks so much! I love this site!!!!

 

[Galaxiom]

No need for such complexity:

WHERE Day([datefield]+ 1)) = 1