DLookup in Query Expressions

[cstickman]

Hi Everyone,

I am trying to get a DLookup to work in a query. I have the following:

Expr1:Left([Phone],3)

The first part takes the 11 string phone column and gets just the area code. Then I have an area code table and I want to associate the state with the area code. So here is what I have currently and it is throwing up a parameter box.

State: DLookup("[AreaCodeST] ","tblareacodes",[tblareacodes].[AreaCode]="Expr1")

I cannot link the tables I do not think since I am using I cannot link the area codes together. Any suggestions on how to make this work?

AreaCodeST is in table tblareacodes
AreaCode is in table tblareacodes

Thanks!!

 

[plog]

You need to store discrete pieces of data discretely. If the area code needs to be used outside of the phone number as a whole, then it should be stored in its own field. When you do that, the query you are after uses a simple JOIN.

To achieve your hack around this, you need to do your Left function in a sub-query. That will artificially get the area code in its own field. Then you can use a simple JOIN to achieve what you want and not the Dlookup.

 

[cstickman]

How do I go about doing that sub-query?

 

[The_Doc_Man]

Queries normally take input from tables, but the truth is that they are not forced to do so. Just as reports and forms can use queries as recordsources, so can a query. So if you create a query that has a separate field containing your Left(...) function, you can make a second query that inputs the first query. And that query CAN participate in a JOIN. Hint: It might be easiest to use the query design grid by including the first query and any other data source. Then your JOIN can be done as a temporary relationship in the graphic table area above the grid. Visually, it would look like you had defined a relationship between a query field and a table field - which is perfectly legal. Then Access will build the correct SQL from that "picture" that you draw for it showing the relationship graphically.

 

[cstickman]

Ok, here is what I have:

Query 1 - labeled (qryareacode)

Select tbltempaccountnotes
F4, Left([F5],3) AS AreaCode
From tbltempaccountnotes
Where tbltempaccountnotes.F4 Like "[*]" & "Call"))

Query - labeled (Query1)

Select tblareacodes.AreaCode, qryareacode.AreaCode
From tblareacodes INNER JOIN qryareacode ON tblareacodes.AreaCode = qryareacode.AreaCode

I get a type mismatch error.

 

[plog]

I get a type mismatch error.

In which query?

I see syntax issues with the first query--

SELECT-->no dot between tbltempaccountnotes and F4
WHERE-->brackets around the * shouldn't be there
WHERE-->2 unmatched right parenthesis

My guess is the mismatch is because of the JOIN in the second query. My guess is your two AreaCode fields are different types--definitely text from qryareacode and my guess is numeric from tblareacodes. In a JOIN you can only match on same datatypes (string to string, date to date, number to number).

 

[cstickman]

That was it!! One was a short text and the other a number. I changed them both to number and it worked. Thanks!!

 

[cstickman]

So I came across a new error while using the query in a liat box. Run-time error 424, Object required. Here is the code:

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs = CurrentDb ()
Set rst = dbs.OpenRecordset ("qrymastercalls", dbOpenDynaset)

Me.lstfd.ColumnCount = 7
RefreshPendingFD
rst.MoveFirst

End sub

Private Sub RefreshPendingFD ()

me.lstfd.RowSource = ""
me.lstfd.ColumnHeads = True
me.lstfd.ColumnWidths = "1in.;2in.;2in.;2in.;2in.;2in.;2in."
me.lstfd.AddItem "Account Number" & ";" & "Call Date" & ";" & "Call Time" & ";" & "Call Type" & ";" & "Phone Number" & ";" & "Area Code State" & ";" & "Status"

Do Until rst.EOF
me.lstfd.AddItem rst! [acctnum] & ";" & rst! [F1] & ";" rst! [F2] & ";" & rst! [Inbound] & ";" & rst! [PhoneNumber] & ";" & rst! [AreaCodeST] & ";" & rst! [Status]
rst.MoveNext
Loop
End Sub

I have used this code in other numerous projects and it works great. When I get the error it highlights Do Until rst.EOF.

Any suggestions?

 

[Minty]

Your rst isn't defined in the sub you are calling. Because you have dimensioned it and set it in the Private Module of the calling sub it's not available in your called sub. You would need to declare it at the top of the whole module directly after the opening declarations;

Option Compare
Option Explicit
Dim rs as Recordset

You would then need to remember to reset it if you use that object again in the same module.

 

[cstickman]

Thanks!! That worked perfectly.