Simple Expression Builder Question

A

Access77

New member

Local time

Today, 02:12

Joined

Aug 30, 2019

Messages

4

• Aug 30, 2019

• #1

I am creating the Access expression builder to create a field in a table, drawing from other fields in the same table.

I have an existing date field in the table that has the format dd/mm/yy.

I want to get the month from this existing date field.

The expression I am using is:

& Month([Date])

This works, but for single digit months (eg January), only return 1

I need the returned value to be 01.

Can someone please assist.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 17:12

Joined

May 7, 2009

Messages

20,336

• Aug 30, 2019

• #2

& Format(Month([Date]), "00")

 

A

Access77

New member

Local time

Today, 02:12

Joined

Aug 30, 2019

Messages

4

• Aug 30, 2019

• #3

arnelgp said:

& Format(Month([Date]), "00")

Click to expand...

I am getting the error message 'The expression cannot be used in a calculated column', though I think you are close.



I have tried variations of this, but so far without success.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 17:12

Joined

May 7, 2009

Messages

20,336

• Aug 30, 2019

• #4

you are entering the expression in Calculated field.
try this expression:

IIf(Month([date]) & ""="","",IIf(Len(Month([date]) & "")<2,"0" & Month([date]) & "",Month([date]) & ""))

 

A

Access77

New member

Local time

Today, 02:12

Joined

Aug 30, 2019

Messages

4

• Aug 30, 2019

• #5

arnelgp said:

you are entering the expression in Calculated field.
try this expression:

IIf(Month([date]) & ""="","",IIf(Len(Month([date]) & "")<2,"0" & Month([date]) & "",Month([date]) & ""))

Click to expand...

Thanks that did it!

 

theDBguy

I’m here to help

Staff member

Local time

Today, 02:12

Joined

Oct 29, 2018

Messages

22,619

• Aug 30, 2019

• #6

Hi. Welcome to AWF! If it’s not too late yet, I would suggest changing the name of your field because Date is a reserved word and can be a cause of headaches later on.

 

A

Access77

New member

Local time

Today, 02:12

Joined

Aug 30, 2019

Messages

4

• Aug 30, 2019

• #7

theDBguy said:

Hi. Welcome to AWF! If it’s not too late yet, I would suggest changing the name of your field because Date is a reserved word and can be a cause of headaches later on.

Click to expand...

Thanks, my field is not named date. I just used this in the example.

 

theDBguy

I’m here to help

Staff member

Local time

Today, 02:12

Joined

Oct 29, 2018

Messages

22,619

• Aug 30, 2019

• #8

Access77 said:

Thanks, my field is not named date. I just used this in the example.

Click to expand...

Okay, sounds good. Looks like this is crossposted here.