#error - Blank

[learning_codes]

Hi,

I'm using query on Access 2007. I was able to convert from text date to actual date.

i.e. 03152014 convert to 03/15/2014

The problem is that when the record has a blank date, it gives me like #Error. I use like:

If([Game_Date] is null, [..convert to Date..],null

The result is that I get like this:

Game_Date Game_Date
03152014 03/15/2014
blank field #error

I decide to create a table from the query and it show blank field but I was not able to put "Is Null".

Can you help me how to make #error changed to blank field from the query if statement. I am really appreciated your help

Thanks

 

[Ulrich Möller]

Am 05.04.2014 07:26, schrieb (e-mail address removed):

Hi,

I'm using query on Access 2007. I was able to convert from text date to actual date.

i.e. 03152014 convert to 03/15/2014

The problem is that when the record has a blank date, it gives me like #Error. I use like:

If([Game_Date] is null, [..convert to Date..],null

The result is that I get like this:

Game_Date Game_Date
03152014 03/15/2014
blank field #error

I decide to create a table from the query and it show blank field but I was not able to put "Is Null".

Can you help me how to make #error changed to blank field from the query if statement. I am really appreciated your help

Thanks

Try something like

iif( len([GAME_DATE]&"")>0, [...ConvertToDate,,,], vbNullstring )

Ulrich

 

[learning_codes]

Am 05.04.2014 07:26, schrieb (e-mail address removed):

Hi,
I'm using query on Access 2007. I was able to convert from text date to actual date.
i.e. 03152014 convert to 03/15/2014
The problem is that when the record has a blank date, it gives me like #Error. I use like:
If([Game_Date] is null, [..convert to Date..],null
The result is that I get like this:
Game_Date Game_Date

03152014 03/15/2014

blank field #error

I decide to create a table from the query and it show blank field but Iwas not able to put "Is Null".

Can you help me how to make #error changed to blank field from the query if statement. I am really appreciated your help

Thanks

Try something like



iif( len([GAME_DATE]&"")>0, [...ConvertToDate,,,], vbNullstring )



Ulrich

Thanks Ulrich,

I tried and it did not work.

Here what I tried to use your example:
IIf(Len([Game_start] & "")>0,Format(CDate(Format([Game_start],"00\/00\/00")),"mm/dd/yyyy"),vbnullstring)

I kept getting error when vbnullstring pop up the message.

 

[Ulrich Möller]

Am 06.04.2014 02:47, schrieb (e-mail address removed):

Am 05.04.2014 07:26, schrieb (e-mail address removed):

Hi,
I'm using query on Access 2007. I was able to convert from text date to actual date.
i.e. 03152014 convert to 03/15/2014
The problem is that when the record has a blank date, it gives me like #Error. I use like:
If([Game_Date] is null, [..convert to Date..],null
The result is that I get like this:
Game_Date Game_Date
03152014 03/15/2014
blank field #error
I decide to create a table from the query and it show blank field but I was not able to put "Is Null".
Can you help me how to make #error changed to blank field from the query if statement. I am really appreciated your help
Thanks

Try something like



iif( len([GAME_DATE]&"")>0, [...ConvertToDate,,,], vbNullstring )



Ulrich

Thanks Ulrich,

I tried and it did not work.

Here what I tried to use your example:
IIf(Len([Game_start] & "")>0,Format(CDate(Format([Game_start],"00\/00\/00")),"mm/dd/yyyy"),vbnullstring)

I kept getting error when vbnullstring pop up the message.

Hi,

firstly you should change the format statement to "Format([Game_start],
"00\/00\/0000")" otherwise you will get an error when you try to convert
the string e.g. "03152014" into a date formatted value.

So the hole line should be:
iif(len([Game_start] & "") > 0, Format([Game_start], "00\/00\/0000"),
vbNullString)

If your destination field type is a date field you have to replace
'vbNullString' in this line with 'null' because a date field cannot hold
an empty string.
Ensure that your input string is always formatted as ddmmyyyy otherwise
you have to create a more complex conversation routine.

Ulrich