If and Then statement to return a null or blank field value

[Padwan]

Hi,
As the user name states I am new at vba for access. I just need some help with a line or 2 of code let me display it first then explain what I need it to do

Private Sub_Report_Open
Dim AnyText As String
If [Remarks TX] ="AnyText" Then
[IWC WC NM1] = Null
Else Cancel
End If
End Sub

I probably wrote that all wrong and I apoligize, but what I need it to do is If the remarks tx field contains text then the iwc wc nm1 field should be displayed as a blank field otherwise leave it alone. It's probably a simple fix like all things in access but getting to that solution is a pain. Is this correct? Any help would be greatly appreciated.

 

[jdraw]

What is Remarks TX?

Are these fields or what [IWC WC NM1]?

Fields and object names SHOULD NOT contain spaces or special characters.

 

[Padwan]

[remarks tx] and [iwc wc nm1] are text fields I know its a cardinal sin but it came from another database of which I had no control of the naming scheme.

 

[Padwan]

If I go through and rename them without the spces should it work?

 

[Padwan]

here is the revised code it runs with now errors but nothing happens
Private Sub Report_Load()
Dim AnyText As String
If [REMARKS TX] = "AnyText" Then
[IWC WC NM1] = Null
End If
End Sub

It picked up the fields no problem with spaces but I think the null portion is not doing anything

 

[thechazm]

The spaces are not the issue. While yes it is normally bad practice to do so it will work non the less. I think the issue is what event you are using.

You have it in the Report_Load() event but the effect your trying to achieve needs to be done for each record. So you should put your code in the CurrentRecord Event of that report.

Your code is not exactly right either but below I have modified it for you.

Private Sub Report_Current()
If IsNull(Me.[REMARKS TX]) Or Me.[REMARKS TX] = "" Then
    Me.[IWC WC NM1].value = Null
End If
End Sub

The code I posted may not work but that is how you would check for a null or blank value on a text field and assign a value if it is.

 

[Padwan]

thanks I will give it a shot. i actually want it to display it as an empty field though

 

[thechazm]

The code posted above does put null in the field lol. It blanks out the field making it blank Good luck

 

[Padwan]

Nope the fields value is still there and the actual field I wanted to change is [CLEARED BY NM] my mistake. The code runs with no compile error but nothing happens. I feel we are getting closer though

Private Sub Report_Current()
If IsNull(Me.[REMARKS TX]) Or Me.[REMARKS TX] = "" Then
Me.[CLEARED BY NM].Value = Null
End If
End Sub

 

[thechazm]

Its problably because its a bound field. I dunno if its going to be possible.

 

[Padwan]

So it only works on unbound fields. Ok i will keep trying to rig it but if it doesn't work It was worth a try thanks anyway for your help

 

[Padwan]

ok I got a temporary solution since I can't touch those fields in access with VBA since they are Bound fields I resorted to deleting the data in [CLEARED BY NM] that has data in the [REMARKS TX] field. It works but it almost defeats the purpose of what I am doing. So until I can find and IF function that can pull it off in MS Excel I have to do it this way-_-.........Does anyone know how to do that in Excel? The table is exactly the same as it is in access It's just imported.

 

[thechazm]

I was just thinking but I think you could do the if statement in your reports record source. By adjusting the SQL code in the report you can make a custom field that mimic's the real one but is controled by your parameters from the get go. Just a thought.

 

[Padwan]

Its record source is a query. Now you got me intrigued I am going to dump in the sql of that query under this text. I never touched sql before so your guidance will be needed on this. If you don't mind.


SELECT APPTSHEET.Name, APPTSHEET.Rank, APPTSHEET.[CLEARED BY NM], APPTSHEET.[CLEARED DT], APPTSHEET.[APPT START DT TM], APPTSHEET.[REQUIRED CLEARANCE DT], IIf([Departure Status]="RETIREMENT","X ","") AS RETIREMENT, IIf([Departure Status]="EXPIRED TERM OF SERVICE","X ","") AS ETS, IIf([Departure Status]="Permanent Change Of Station","X ","") AS PCS, IIf([Departure Status]="OTHER","X ","") AS Other, IIf([Departure Status]="RETIREMENT"," TRANSITION CENTER",IIf([Departure Status]="EXPIRED TERM OF SERVICE"," TRANSITION CENTER",IIf([Departure Status]="OTHER"," TRANSITION CENTER"))) AS [TRANSITION CENTER], IIf([Departure Status]="Permanent Change Of Station","STEPHAN HUBER","") AS Finance, APPTSHEET.[PIP REMARKS TX], APPTSHEET.[Departure Status], APPTSHEET.[IWC WC NM1], APPTSHEET.SSN, APPTSHEET.[REMARKS TX]
FROM APPTSHEET
WHERE (((APPTSHEET.[IWC WC NM1])<>("SEPARATION PAY PROCESSING") And (APPTSHEET.[IWC WC NM1])<>("TRAVEL PAY PROCESSING") And (APPTSHEET.[IWC WC NM1])<>("CG RELEASE MEMORANDUM") And (APPTSHEET.[IWC WC NM1])<>("HAAF EENT") And (APPTSHEET.[IWC WC NM1])<>("HAAF FINAL OUT") And (APPTSHEET.[IWC WC NM1])<>("HAAF FINANCE") And (APPTSHEET.[IWC WC NM1])<>("HAAF PROCESSING CONTOL STATION") And (APPTSHEET.[IWC WC NM1])<>("HAAF SHOPETTE") And (APPTSHEET.[IWC WC NM1])<>("CG RELEASE MEMORANDUM") And (APPTSHEET.[IWC WC NM1])<>("LODGING OFFICE")));

 

[thechazm]

lol the answer is already inside your current SQL.

Look at this part of your SQL

IIf([Departure Status]="RETIREMENT","X ","") AS RETIREMENT

What this does in a nutsheel it is evaluates if true or not. And the field name you would use on your report as a bound control would be RETIREMENT.

I hope I have explained this good enough but simply put you just need to add a specific iff statement in with the other iff statements and you should be good to go.

 

[Padwan]

Thanks, say I do an IIF statement like this

IIf([REMARKS TX]="NotNull","","")

and place it in the criteria portion of the [CLEARED BY NM] column in that query will it work or is "NotNull" the wrong word to use. I need to pick up any text that happens to be in that field and since everyones remarks is going to be different I can't place a exact word or words in there.

 

[Padwan]

nope it doesn't. I get a blank screen return on my query. This is getting frustrating.

 

[thechazm]

Your pretty close with your code.

IIf([REMARKS TX]="NotNull","","")

I would actually change this to:

IIf([REMARKS TX]=Null,Null,[REMARKS TX])

that way if the value is null then it just makes it blank if its not then it puts in the value in the field.

 

[Padwan]

it works but I need to display the value in the [CLEARED BY NM] not [REMARKS TX]

CLEARED BY NM1: IIf([REMARKS TX]=Null,Null,[CLEARED BY NM])

Kinda like that. I had to name the expression that because of a circular reference error from access even after I nuked the field from my query
So If remarks has a comment then the corresponding [CLEARED BY NM] field for that row is then blank. If remarks is blank then [CLEARED BY NM] is left alone. I then have that result in place of [CLEARED BY NM] on the query.

 

[Padwan]

now if it only worked lol