Extracting Two Fields with Same Name from SQL Query (VBA)

[Mav3000]

Hi,

I have a SQL query I am running in a form which pulls in some fields from two tables. Two of the fields, one from each table, has the same name.

How can I reference each field specifically in order to refer to the correct field?

Everytime I try anything other than the following, I get errors saying that the field doesn't exist:

SQL Query:
==========

sSQL = "SELECT " & _
"tblMobClients.Name, " & _
"tblMobContracts.ContractID, " & _
"tblMobContracts.Reference, " & _
"tblMobContracts.Name " & _
"FROM " & _
"tblMobClients, " & _
"tblMobContracts " & _
"WHERE " & _
"tblMobContracts.ClientID = tblMobClients.ClientID " & _
"ORDER BY " & _
"Reference"

Populate Array with Data:
=========================

While Not oM2.EOF
sContractArray(i, 0) = oM2("ContractID")
sContractArray(i, 1) = oM2("Reference")
sContractArray(i, 2) = oM2("Name") 'SAME NAME
sContractArray(i, 3) = oM2("Name") 'SAME NAME

I'd like sContractArray(i, 2) = tblMobClients.Name
and sContractArray(i, 3) = tblMobContracts.Name

How do I distinguish between the two?

Thanks,

Mav3000

 

[Andrzejek]

The 'Name' is not very good *name* for the field in DB

But to fix your problem, use aliases:

    sSQL = "SELECT " & _
                "tblMobClients.Name [blue]As MyName[/blue], " & _
                "tblMobContracts.ContractID, " & _
                "tblMobContracts.Reference, " & _
                "tblMobContracts.Name[blue] As YourName [/blue] " & _
            "FROM " & _
                "tblMobClients, " & _
                "tblMobContracts " & _
            "WHERE " & _
                "tblMobContracts.ClientID = tblMobClients.ClientID " & _
            "ORDER BY " & _
                "Reference"

....

    While Not oM2.EOF
        sContractArray(i, 0) = oM2("ContractID")
        sContractArray(i, 1) = oM2("Reference")
        sContractArray(i, 2) = oM2("[blue]MyName[/blue]")
        sContractArray(i, 3) = oM2("[blue]YourName[/blue]")

Have fun.

---- Andy

 

[Mav3000]

Thanks Andy that's exectly what I needed. I didn't name the fields - I'm the person that has to use them! so from now on I'll be using a lot of aliases. A new concept for me.

Thanks again - and for such a quick reply.

Mav3000

 

[Andrzejek]

Glad to help.

As a side note, you can always refear to the field by its order in your recordset:

    sSQL = "SELECT " & _
                "tblMobClients.Name, " & _
                "tblMobContracts.ContractID, " & _
                "tblMobContracts.Reference, " & _
                "tblMobContracts.Name " & _
            "FROM " & _
                "tblMobClients, " & _
                "tblMobContracts " & _
            "WHERE " & _
                "tblMobContracts.ClientID = tblMobClients.ClientID " & _
            "ORDER BY " & _
                "Reference"

....

    While Not oM2.EOF
        sContractArray(i, 0) = oM2([blue]2[/blue])
        sContractArray(i, 1) = oM2([blue]3[/blue])
        sContractArray(i, 2) = oM2([blue]1[/blue])
        sContractArray(i, 3) = oM2([blue]4[/blue])

But I would not recomend it. I would rather use aliases.

And if you like aliases, you can also alias the names of the tables:

    sSQL = "SELECT " & _
                "[blue]Client[/blue].Name, " & _
                "[blue]Contract[/blue].ContractID, " & _
                "[blue]Contract[/blue].Reference, " & _
                "[blue]Contract[/blue].Name " & _
            "FROM " & _
                "tblMobClients [blue]Client[/blue], " & _
                "tblMobContracts [blue]Contract[/blue]" & _
            "WHERE " & _
                "[blue]Contract[/blue].ClientID = [blue]Client[/blue].ClientID " & _
            "ORDER BY [blue]3[/blue]"

I used it when I have really long table names, like [tt]S4123999.ABCContacts_Information[/tt]

Have fun.

---- Andy