Date serial formula help please 1

[misscrf]

here is the formula:

=DateSerial(Year(Left([BILL_DATE],4)),Month(Mid([BILL_DATE],5,2)),Day(Right([BILL_DATE],2)))

It is throwing the #error.

The field is a number, double.

An example of the data is 20040931
September 09, 2004 is what I would like, but I will settle for 09-31-2004.

Thanks.

misscrf

Management is doing things right, leadership is doing the right things

 

[lupins46]

=DateSerial(Left([BILL_DATE],4),Mid([BILL_DATE],5,2),Right([BILL_DATE],2))

 

[misscrf]

That still give me the #error. Thank you for trying.

Any other suggestions?

misscrf

Management is doing things right, leadership is doing the right things

 

[lupins46]

I assume you are not really using
20040931
as it is not a valid date.

 

[misscrf]

I dont think I understand your question.

The field is [BILL_DATE] which is a double numeric field, and an example of the field's data is 20040931 or 20041020. Does this make sense?

misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

I would not mess around with using string functions on a number to return a number. Also
Month(Mid([BILL_DATE],5,2)) will always return either December or January (think about it). Also, September has only 30 days, not 31.

Try:
=DateSerial([billdate]\10000,([billdate] mod 10000)\100,[billdate] mod 100)

A BillDate of 20040931 in the above expression returns 10/1/2004.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

I still get #error. I didnt mean to give you an example that isnt a valid date. a valid example would be 20040930 or 20041020.

It is a number field. Should I try something other than dateserial, like maybe a formula to switch the numbers around?

ie if I had a formula that would take the 20049030 and make it 09302004 then I could just use the format property of the field to specify date, couldnt I ?

Is this possible/how?


misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

I think DateSerial is the best method to use. Are there any null values in the field? I tried the expression that I used and it worked as expected. What is the exact expression that you entered into your control source on the report?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

The expression I entered is :
=DateSerial([billdate]\10000,([billdate] mod 10000)\100,[billdate] mod 100)

There are no nulls. This is data that we are just starting to get, and I am creating the database so that we can report on the data. Right now, every record ( all 4000+) have the same date, because their bill date is the same... 20040915. Bills either come on the first or the 15th, not that that matters.

Anyway, any reason why I get the #error, when your formula should work?

Thanks

misscrf

Management is doing things right, leadership is doing the right things

 

[misscrf]

I figured it out. I found a post on utteraccess...

here is the expression I used to make it "look nice"

Bill: Mid([BILL_DATE],5,2) & "/" & Right([BILL_DATE],2) & "/" & Left([BILL_DATE],4)

here is the url where I got it:

http://www.accessvba.com/showthread.php?s=&threadid=935&highlight=format+numeric+as+date

misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

I shudder at using mid, right, left and "&" to build a date value.
To test my expression, I opened the debug window (press Ctrl+G) and entered the following two lines and pressed enter after each:

BillDate=20040915
? DateSerial(billdate\10000,(billdate mod 10000)\100,billdate mod 100)
9/15/2004

If you try the same, you should get the same final result of 9/15/2004.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.