Case statement

[GOVMATE]

Hello, I'm attempting to use a case statement in order to select fields from a column that have names and addresses mixed. I'm wanting to select only the fields that contain names. For example some fields start with an address of "1998 Sky Rd" or PO BOX, or Suite at the beginning of the field. I would like to first select all fields with names in the field and then use an update statement to move/switch fields to another column. The only problem is that I can't seem to get this query to work. Please help!!!

Thank you!!!!!!!!!


SELECT address1, name3=address1 as expr1

Case [address1]

When mid([address1],1,1) Like [A-Z] then [address1]
When [address] is null then ""
When [address] = "" then ""
Else 'Null"
End,

FROM Exercise1;

 

[CraigDolphin]

Gov,

your question confuses me. I can;t actually figure out what you are wanting to do. You can't select fields from a column: a column is a field.

Instead, why don't you show a few records of some sample input data and a sample of how you want that data to look after your query has run and see if that clarifies the situation.

 

[boblarson]

I would also add that from your posting it is pretty obvious that you aren't too familiar with how a select case statement works in Access.

Your syntax is off - it should be:

Select Case "some value here or reference to a control"

Case "whatever"
    Do something here

Case "Another whatever"
   Do something here

Case Else
   Do something if it doesn't match any of the named cases

End Select

So, an example would be if I had a control like an combo box where I selected an item in it and I wanted to open a report based on the selection:

Select Case Me.ComboNameHere

Case "Main"
    DoCmd.OpenReport "rptMain", acViewPreview
Case "Accounting"
    DoCmd.OpenReport "rptAccounting", acViewPreview
Case "Engineering"
    DoCmd.OpenReport "rptEngineering", acViewPreview
Case Else
   Msgbox "You didn't select a report that is active.  Please try again", vbExclamation, "No Valid Report"
End Select

 

[pbaldy]

That looks like a T-SQL CASE statement. The nearest equivalent in Access would be an IIf() function.

 

[GOVMATE]

Gov,

your question confuses me. I can;t actually figure out what you are wanting to do. You can't select fields from a column: a column is a field.

Instead, why don't you show a few records of some sample input data and a sample of how you want that data to look after your query has run and see if that clarifies the situation.

Ok,

Thanks for the input Craig....I'm thinking that I'll just stick with an Iif statement to solve my issue.

 

[GOVMATE]

That looks like a T-SQL CASE statement. The nearest equivalent in Access would be an IIf() function.

Yea I was able to do it with an Iif statement....I've never used an case statement and I was told to try it out.

Thanks Pbaldy!!!!!

 

[GOVMATE]

I would also add that from your posting it is pretty obvious that you aren't too familiar with how a select case statement works in Access.

Your syntax is off - it should be:

Select Case "some value here or reference to a control"

Case "whatever"
    Do something here

Case "Another whatever"
   Do something here

Case Else
   Do something if it doesn't match any of the named cases

End Select

So, an example would be if I had a control like an combo box where I selected an item in it and I wanted to open a report based on the selection:

Select Case Me.ComboNameHere

Case "Main"
    DoCmd.OpenReport "rptMain", acViewPreview
Case "Accounting"
    DoCmd.OpenReport "rptAccounting", acViewPreview
Case "Engineering"
    DoCmd.OpenReport "rptEngineering", acViewPreview
Case Else
   Msgbox "You didn't select a report that is active.  Please try again", vbExclamation, "No Valid Report"
End Select

Ok I appreciate the insight Bob, and yes I've never used the case statement before. I was told by a co-worker that it might be a better solution that using "iif" but I'm finding that an "iif statment" suits my purpose better.

Thanks

 

[pbaldy]

Just to clarify a little, there is a Case statement in Access VBA (as part of the Select/Case structure), which is what Bob was demonstrating. It is different than the T-SQL CASE statement that you originally had, and it won't work directly in a query as you were trying to use it. It does work directly in a query in T-SQL.

 

[boblarson]

Just to clarify a little, there is a Case statement in Access VBA (as part of the Select/Case structure), which is what Bob was demonstrating. It is different than the T-SQL CASE statement that you originally had, and it won't work directly in a query as you were trying to use it. It does work directly in a query in T-SQL.

Exactly - my braid was not grasping that you were doing this in a SQL statement and therefore would have been different from what I was referring to.

 

[GOVMATE]

Just to clarify a little, there is a Case statement in Access VBA (as part of the Select/Case structure), which is what Bob was demonstrating. It is different than the T-SQL CASE statement that you originally had, and it won't work directly in a query as you were trying to use it. It does work directly in a query in T-SQL.

Alright,

I really appreciate the insight...learning something new everyday!!!!