 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / October 2007IF STATEMENT
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? 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 [quoted text clipped - 3 lines] > '000000' appears then "Lifetime" will appear otherwise the "010107" > will appear as 01/01/07. Is this possible? 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" <GeorgeNJ...@Junkmsn.com> 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 [quoted text clipped - 17 lines] > > - Show quoted text - 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 [quoted text clipped - 26 lines] >> >> - Show quoted text - REplace MyField with the actual name of the field. I am assuming that you don't have a field named myField.  SignatureJohn 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 [quoted text clipped - 26 lines] >> >> - Show quoted text - 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. [quoted text clipped - 42 lines] > > - Show quoted text - 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 SignatureJohn 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. [quoted text clipped - 57 lines] >> >> - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.