Need help with DLookUp using Format Function 2

[ImStuk]

Can somebody please tell me where I went wrong with this function. I feel like I am so close. Thanks in advance for any help you can provide.

="Phone: " & Format(DLookUp("[CustomersPhoneNumber]","tblCustomers","(&&&) &&&-&&&&&quot,"AccountNumber = " & [DBAccountNumber]) & " Fax: " & Format(DLookUp("[CustomersFaxNumber]","tblCustomers","(&&&) &&&-&&&&&quot,"AccountNumber = " & [DBAccountNumber])

 

[Darrylles]

Hi,

It would help if you hinted at the result you are getting that you don't like.

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[vbajock]

I don't see any single quotes for your string values. You also should separate the stuff out into variables to make it more manageable and easier to troubleshoot:

Dim sPhone as string
Dim Fax as string

sPhone = DLookUp("[CustomersPhoneNumber]","tblCustomers",
"AccountNumber = '" & [DBAccountNumber] & "'&quot
sPhone=format(sPhone,"(&&&) &&&-&&&&&quot

sFax=DLookUp("[CustomersFaxNumber]","tblCustomers",
"AccountNumber = '" & [DBAccountNumber] & "'&quot
sFax=format$(sFax,,"(&&&) &&&-&&&&&quot

This:
="Phone: "
is a mystery to me. What are you trying to do here?

 

[ImStuk]

It is just saying error in the text box I am trying to use this in. I am trying to put a fax number and phone number on a report in one text box. I need to use criteria, and also format the phone numbers or they just come out like this 6195551212 with no dashes. I don't get a syntex error, but on the Preview of the report it just says #error

 

[ImStuk]

vbaJock, I got your answer after sending my first reply. This is how I want the line to read in one text box on my report:

Phone: (619) 555-1212 Fax: (619) 555-1313

 

[vbajock]

You need to test dlookup for nulls, too, so it should look like this:

Dim sPhone as string
Dim sFax as string

'Phone#
if isnull(DLookUp("[CustomersPhoneNumber]","tblCustomers",

"AccountNumber = '" & [DBAccountNumber] & "'&quot) then
sPhone="None"
else
sPhone=DLookUp("[CustomersPhoneNumber]","tblCustomers",

"AccountNumber = '" & [DBAccountNumber] & "'&quot

endif
sPhone=format$(sPhone,"(&&&) &&&-&&&&&quot
'Fax#
if isnull(DLookUp("[CustomersFaxNumber]","tblCustomers",

"AccountNumber = '" & [DBAccountNumber] & "'&quot) then
sFax="None"
else
sFax=DLookUp("[CustomersFaxNumber]","tblCustomers",

"AccountNumber = '" & [DBAccountNumber] & "'&quot

endif
sFax=format$(sFax,"(&&&) &&&-&&&&&quot
'display

Forms!myForm!mytextbox="Phone: "+sPhone+" Fax: "+SFax
Forms!myForm.repaint

 

[vbajock]

Also, I don't know where your getting [DBAccountNumber]. If its coming off your active form,name the text box it is in to DBAccountNumber and change [DBAccountNumber] to
Me!DbAccountNumber

Should look like
DLookUp("[CustomersFaxNumber]","tblCustomers",
"AccountNumber = '" & Me!DbAccountNumber & "'&quot)

 

[GeekGirlau]

You can simplify the problem of null values by declaring your variables as Variant, which will accept null values.

Dim varPhone as Variant
Dim varFax as Variant

'Phone#
varPhone = DLookUp("[CustomersPhoneNumber]","tblCustomers",_
"AccountNumber = '" & Me![DBAccountNumber] & "'&quot

If IsNull(varPhone) Then
varPhone = "None"
Else
varPhone = Format(varPhone,"(&&&) &&&-&&&&&quot
End If

'Fax#
varFax = DLookUp("[CustomersFaxNumber]","tblCustomers",_
"AccountNumber = '" & [DBAccountNumber] & "'&quot

If IsNull(varFax) Then
varFax = "None"
Else
varFax = Format(varFax,"(&&&) &&&-&&&&&quot
End If

With Forms!myForm
!mytextbox="Phone: " & varPhone & " Fax: " & varFax
.repaint
End With

 

[vbajock]

Yeah, but variants are being phased out so I avoid using them so because of the possibility of conversion hassles later.

 

[ImStuk]

Thanks for all the help. I was finally able to get it to work this way in the Text box Control Source. I had the format in the wrong place. It may not be the most efficient, but it works.

="Phone: " & Format(DLookUp("[CustomersPhoneNumber]","tblCustomers","AccountNumber = " & [DBAccountNumber]),"(&&&) &&&-&&&&&quot & " Fax: " & Format(DLookUp("[CustomersFaxNumber]","tblCustomers","AccountNumber = " & [DBAccountNumber]),"(&&&) &&&-&&&&&quot