IF STATEMENT

[Hendrix10]

I imported a txt file into access and one of the fields is a date
field. My problem is that some of the data in the field show up as
'000000' because there is no date and the rest is a date (010107). So,
when I brought it into access the column shows up blank when I make it
a date field. I would like to set up an IF statement so that If
'000000' appears then "Lifetime" will appear otherwise the "010107"
will appear as 01/01/07. Is this possible?

 

[George Nicholson]

Doesn't sound like a you have a date field. 010107 expressed as a date is
Sept 2 1927 (the number of days since 1/1/1900 which is how dates are
handled in Excel and Access).

What you seem to have is a text representation of a date.
If that's the case then:
iif(myField = "000000", "Lifetime", Format(myField,"00/00/00"))

 

[Hendrix10]

I tried the below and the column was filled with "Myfield". I assumed
this was because access put "" around myfield in the statemtent. So, I
went back and put [] around "myfield" and received and error message.
Also, the 1st time I ran it and went back to design view, what I put
in as "00/00/00" now looked like "00\/00\/00". Can someone tell me
what I've done wrong?

 

[George Nicholson]

1) you need to replace Myfield with the actual name of your field. and yes,
I should have said [MyField] (no quotes).

2) "00\/00\/00" is fine. The \ is an instruction to use the next character
(/) literally (see Online VB help for the Format function). Sometimes it's
required, sometimes not, sometimes Access "helps" and adds it on its own.

HTH,

I tried the below and the column was filled with "Myfield". I assumed
this was because access put "" around myfield in the statemtent. So, I
went back and put [] around "myfield" and received and error message.
Also, the 1st time I ran it and went back to design view, what I put
in as "00/00/00" now looked like "00\/00\/00". Can someone tell me
what I've done wrong?

Doesn't sound like a you have a date field. 010107 expressed as a date is
Sept 2 1927 (the number of days since 1/1/1900 which is how dates are
handled in Excel and Access).

What you seem to have is a text representation of a date.
If that's the case then:
iif(myField = "000000", "Lifetime", Format(myField,"00/00/00"))






- Show quoted text -

 

[John Spencer]

REplace MyField with the actual name of the field. I am assuming that you
don't have a field named myField.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I tried the below and the column was filled with "Myfield". I assumed
this was because access put "" around myfield in the statemtent. So, I
went back and put [] around "myfield" and received and error message.
Also, the 1st time I ran it and went back to design view, what I put
in as "00/00/00" now looked like "00\/00\/00". Can someone tell me
what I've done wrong?

Doesn't sound like a you have a date field. 010107 expressed as a date is
Sept 2 1927 (the number of days since 1/1/1900 which is how dates are
handled in Excel and Access).

What you seem to have is a text representation of a date.
If that's the case then:
iif(myField = "000000", "Lifetime", Format(myField,"00/00/00"))






- Show quoted text -

 

[Hendrix10]

I did change the "myfield" to what it is in my table and now it's just
gave me a totally different result.

This is what the SQL looks like:

WHERE (((GSS3.
[myfield])=IIf([myfield]="000000","Lifetime",Format([myfield],"00\/00\/
00"))));

Is this what it's supposed to look like?

REplace MyField with the actual name of the field. I am assuming that you
don't have a field named myField.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I tried the below and the column was filled with "Myfield". I assumed
this was because access put "" around myfield in the statemtent. So, I
went back and put [] around "myfield" and received and error message.
Also, the 1st time I ran it and went back to design view, what I put
in as "00/00/00" now looked like "00\/00\/00". Can someone tell me
what I've done wrong?

- Show quoted text -

 

[John Spencer]

In a column to display the value you would use

FIELD: ShowStuff:
IIf([myfield]="000000","Lifetime",Format([myfield],"00\/00\/00"))

In a SQL statement that would look like

SELECT IIf([myfield]="000000","Lifetime",Format([myfield],"00\/00\/00")) as
ShowStuff
, [AnotherFieldName]
, [SomeOtherField]
FROM SomeTable


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I did change the "myfield" to what it is in my table and now it's just
gave me a totally different result.

This is what the SQL looks like:

WHERE (((GSS3.
[myfield])=IIf([myfield]="000000","Lifetime",Format([myfield],"00\/00\/
00"))));

Is this what it's supposed to look like?

REplace MyField with the actual name of the field. I am assuming that
you
don't have a field named myField.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I tried the below and the column was filled with "Myfield". I assumed
this was because access put "" around myfield in the statemtent. So, I
went back and put [] around "myfield" and received and error message.
Also, the 1st time I ran it and went back to design view, what I put
in as "00/00/00" now looked like "00\/00\/00". Can someone tell me
what I've done wrong?

On Oct 17, 5:08 pm, "George Nicholson" <[email protected]>
wrote:
Doesn't sound like a you have a date field. 010107 expressed as a date
is
Sept 2 1927 (the number of days since 1/1/1900 which is how dates are
handled in Excel and Access).

What you seem to have is a text representation of a date.
If that's the case then:
iif(myField = "000000", "Lifetime", Format(myField,"00/00/00"))

I imported a txt file into access and one of the fields is a date
field. My problem is that some of the data in the field show up as
'000000' because there is no date and the rest is a date (010107).
So,
when I brought it into access the column shows up blank when I make
it
a date field. I would like to set up an IF statement so that If
'000000' appears then "Lifetime" will appear otherwise the "010107"
will appear as 01/01/07. Is this possible?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -