Help writing IF/ Else statement in Access SQL query

[DeltaEchoMikeIT]

Good Evening All,

My query involves a database that reserves and books out loan equipment to users. When The database was originally designed it took no account of users who as the result of marriage might change their surnames. This presents some issues when those users return under a different name.

I have created a search form that allows the administrator to check their surname against their userID number, then add the new surname into a new field called NameChange, within the user table. This part of the procedure went without incident, but I would like to take this a step further.

At the point of making the reservation, I would like to adjust the existing SQL query, to search staff accounts and update only those surnames that have been data inputted into the NameChange field. This would then reflect in the user search and update reservation and booking information.

The Reservation Form uses a query to search all users in the Tbl_Person ordered by surname. With the new field NameChange, the SQL runs OK as follows:

 SELECT tbl_Person.PersonID, [Surname] & "(" & [NameChange] & ")" & ", " & [Forename] & " (" & [PersonID] & ")" AS Name, tbl_Person.NameChange
FROM tbl_Person
ORDER BY tbl_Person.Surname;

The code appends the new name in brackets to the maiden name. I would like it to replace the maiden name and was thinking that an IF/Else statement would do it. If so, I would be most grateful for any assistance writing it.
The statement would be along the lines...

If Not Null [NameChange] then Select tbl_Person.ID,[NameChange]]& ")" & ", " & [Forename] & " (" & [PersonID] & ")" AS Name, tbl_Person.NameChange
FROM tbl_Person
ORDER BY tbl_Person.Surname;

Else Select tbl_Person.ID,[Surname]& ")" & ", " & [Forename] & " (" & [PersonID] & ")" AS Name, tbl_Person.NameChange
FROM tbl_Person
ORDER BY tbl_Person.Surname;

This is my first go at SQL and I attempted to parse the above speculative code but it wouldn't go. If anyone can offer any advice and guidance that would be great.

Thanks in advance.

[Oralloy]

DeltaEchoMikeIT ,

This might be helpful: https://msdn.microsoft.com/en-us/library/hh213574.aspx

ref: https://support.office.com/en-us/art...0-647539c764e3
ref: https://support.office.com/en-us/art...3-aba746fb29d8

I'm doing this blind, but this might work for you:

SELECT
  ID AS "Identifier",
  iif(NameChange IS NULL, 
    ([Surname]&")"&", "&[Forename]&" ("&[PersonID]&")"),
    ([Surname]&"("&[NameChange]&")"&", "&[Forename]&" ("&[PersonID]&")"))
    AS "Composite-Name",
  NameChange AS "Name-Change"
FROM tbl_Person
ORDER BY Surname;

[DeltaEchoMikeIT]

Thanks for that code Oralloy. Only needed to do a couple of adjustments and then it worked like a charm.

[Oralloy]

DeltaEchoMikeIT ,

Will you do all of us the favour and post what you use, which works?

That way other people can learn from what you've done.

--Oralloy

[DeltaEchoMikeIT]

Here is the exact code I used. I adjusted Oralloy's code suggestion, which really created a composite name of the original surname and the surname change. I wanted the surname change to replace the original. I used the query text fields ["Full-NameChange"] and ["Name-Change"]to query and return to a form the full name and surname changes, respectively. Here it is:

[CODE]
SELECT tbl_Person.Pers onID AS ["Identifier "], IIf([NameChange] Is Null,([Surname] & ", " & [Forename] & " (" & [PersonID] & ")"),([NameChange] & ", " & [Forename] & " (" & [PersonID] & ")")) AS ["Full-NameChange"], tbl_Person.Name Change AS ["Name-Change"]
FROM tbl_Person
ORDER BY IIf([NameChange] Is Null,([Surname] & ", " & [Forename] & " (" & [PersonID] & ")"),([NameChange] & ", " & [Forename] & " (" & [PersonID] & ")"));
[CODE/]

[DeltaEchoMikeIT]

I have another query...followi ng on from the above...

As already mentioned, where appropriate I have applied the code for the manually generated text fields (["Full-NameChange"] and ["Name-Change"]. These query the name changes to apply to fields sitting behind the forms used to enter and reserve users against loan equipment. I have applied this successfully to several forms, but there is one form with 6 comboboxes that defies the usual fixes because they have some After Event Update VBA to update the equipment and user filter data. The VBA that filters on Surname is as follows:

[CODE]
Private Sub Combo18_AfterUp date()
Dim myCustomerSN As String

myCustomerSN = "select * from LoansReservatio ns where (Sur-NameChange ='" & Me.Combo18 & "')"
Me.LoansReserva tionsSubform.Fo rm.RecordSource = myCustomerSN
Me.LoansReserva tionsSubform.Fo rm.Requery
Me.Combo8 = Null
Me.Combo6 = Null
Me.Combo14 = Null
Me.Combo12 = Null
Me.Combo33 = Null
End Sub
[CODE/]

The problem is with the opening line of VBA..
[CODE]
myCustomerSN = "select * from LoansReservatio ns where ([Sur-NameChange] ='" & Me.Combo18 & "')"
[CODE/]

Since the manually created text field [Sur-NameChange] that checks and returns a surname change now replaces the original[Surname] field, it invokes a parameter value search box. I assume this is because the manual text field should be enclosed in inverted commas, like ["Sur-NameChange"]. The problem is that the comma enclosed field will not parse in the VBA.

Does anyone have advice how I can code this?

Thanks

[DeltaEchoMikeIT]

OK I have solved the After Event Update issue in VBA. It was simple. Oralloy's solution to create the parameters as text fields (eg...

  iif(NameChange IS NULL..."[NameChange]&")"&"...
    AS "Composite-Name",
  NameChange AS "Name-Change"

Works only for ordinary parameter queries with no related VBA coding. If the query is then translated into an After event update,for instance, the VBA does not recognise the field in inverted commas. The solution, then, is to go back to the parameter query and remove the inverted commas from the parameter name for the query. The VBA name, without the inverted commas, then recognises the field name.

Orally, I understand why you presented your SQL Select Query as fields enclosed in inverted commas since it is a way of differenting between normal fields and those derived by calculations or parameter queries. For anyone getting aquainted with VBA coding, however, should mark this form of notation as a car crash.