Problem with DLookup

T

Tony Williams

Can anyone tell me why I'm not getting an answer to this in a calculated
control
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")

tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are controls
on a page on a tabcontrol on a form and txtqtr1 is a calculated control
using
=DateAdd("m",-3,[txtmonthlabel])

I've tried it with [ ] brackets and without but I don't get any value shown.

The form is based on a query which is based on my table tblmaintabs
TIA
Tony
 
D

DebbieG

If txtqtr1 is text it would look like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = ' " & me.txtqtr1 & " ' " )

If txtqtr1 is a date it would look like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = # " & me.txtqtr1 & " # " )

if txtqtr1 is numeric it would look like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = & me.txtqtr1 )

HTH,
Debbie


| Can anyone tell me why I'm not getting an answer to this in a calculated
| control
| =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")
|
| tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are controls
| on a page on a tabcontrol on a form and txtqtr1 is a calculated control
| using
| =DateAdd("m",-3,[txtmonthlabel])
|
| I've tried it with [ ] brackets and without but I don't get any value shown.
|
| The form is based on a query which is based on my table tblmaintabs
| TIA
| Tony
|
|
 
F

fredg

Can anyone tell me why I'm not getting an answer to this in a calculated
control
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")

tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are controls
on a page on a tabcontrol on a form and txtqtr1 is a calculated control
using
=DateAdd("m",-3,[txtmonthlabel])

I've tried it with [ ] brackets and without but I don't get any value shown.

The form is based on a query which is based on my table tblmaintabs
TIA
Tony
Click to expand...

Your syntax is incorrect.
It depends upon the Datatype of the Field used as criteria.
What is the datatype of [txtMonthLabel]?
If it is a text datatype, you would write the where clause like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = '" &
[ControlNameOnform] & "'")

If however, [txtMontLabel] is a Number datatype,then use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = " &
[ControlNameOnform])

If your [txtMonthLabel] is a Date datatype, as it appears to be, then:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = #" &
[ControlNameOnform] & "#")

So, using your field names, you want to use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel] = #"
[txtqtr1] & "#")
 
T

Tony Williams

Thanks to you both for those answers I've used them to tidy up my code
Tony
fredg said:
Can anyone tell me why I'm not getting an answer to this in a calculated
control
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")

tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are controls
on a page on a tabcontrol on a form and txtqtr1 is a calculated control
using
=DateAdd("m",-3,[txtmonthlabel])

I've tried it with [ ] brackets and without but I don't get any value shown.

The form is based on a query which is based on my table tblmaintabs
TIA
Tony
Click to expand...

Your syntax is incorrect.
It depends upon the Datatype of the Field used as criteria.
What is the datatype of [txtMonthLabel]?
If it is a text datatype, you would write the where clause like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = '" &
[ControlNameOnform] & "'")

If however, [txtMontLabel] is a Number datatype,then use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = " &
[ControlNameOnform])

If your [txtMonthLabel] is a Date datatype, as it appears to be, then:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = #" &
[ControlNameOnform] & "#")

So, using your field names, you want to use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel] = #"
[txtqtr1] & "#")
Click to expand...
 
T

Tony Williams

Debbie txtqtr1 is a date type but I now get an error message which says
expression contains invalid date value.
Any ideas
 
T

Tony Williams

Fred tried you code and got syntax error message You may have entered an
operand without an operator?
Any ideas?
Thanks
Tony
fredg said:
Can anyone tell me why I'm not getting an answer to this in a calculated
control
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")

tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are controls
on a page on a tabcontrol on a form and txtqtr1 is a calculated control
using
=DateAdd("m",-3,[txtmonthlabel])

I've tried it with [ ] brackets and without but I don't get any value shown.

The form is based on a query which is based on my table tblmaintabs
TIA
Tony
Click to expand...

Your syntax is incorrect.
It depends upon the Datatype of the Field used as criteria.
What is the datatype of [txtMonthLabel]?
If it is a text datatype, you would write the where clause like this:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = '" &
[ControlNameOnform] & "'")

If however, [txtMontLabel] is a Number datatype,then use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = " &
[ControlNameOnform])

If your [txtMonthLabel] is a Date datatype, as it appears to be, then:

=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = #" &
[ControlNameOnform] & "#")

So, using your field names, you want to use:
=DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel] = #"
[txtqtr1] & "#")
Click to expand...
 
D

DebbieG

Tony,

What is in txtMonthlabel? What is it's Data Type and Format?
What is txtqtr1's Format?
What is the name of control you're using this DlookUp in?
What is in txtDomfacsoleqtr?

I'm confused now ... you stated "tblmaintabs is my table and txtDomfacsoleqtr
and txtMonthlabel are controls on a page on a tabcontrol on a form". If
txtDomfacsoleqtr is already on the form for that record, why are you looking it
up?

I found this in DateAdd's Help:

The format of the return value for DateAdd is determined by Control Panel
settings, not by the format that is passed in date argument. If month values
are names, the name must be consistent with the current Calendar property
setting. To minimize the possibility of month names conflicting with the current
Calendar property setting, enter numeric month values (Short Date format).

Debbie

| Debbie txtqtr1 is a date type but I now get an error message which says
| expression contains invalid date value.
| Any ideas
| | > If txtqtr1 is text it would look like this:
| >
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = ' " &
| me.txtqtr1 & " ' " )
| >
| > If txtqtr1 is a date it would look like this:
| >
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = # " &
| me.txtqtr1 & " # " )
| >
| > if txtqtr1 is numeric it would look like this:
| >
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]" = &
| me.txtqtr1 )
| >
| > HTH,
| > Debbie
| >
| >
| | > | Can anyone tell me why I'm not getting an answer to this in a calculated
| > | control
| > |
| =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")
| > |
| > | tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are
| controls
| > | on a page on a tabcontrol on a form and txtqtr1 is a calculated control
| > | using
| > | =DateAdd("m",-3,[txtmonthlabel])
| > |
| > | I've tried it with [ ] brackets and without but I don't get any value
| shown.
| > |
| > | The form is based on a query which is based on my table tblmaintabs
| > | TIA
| > | Tony
 
D

DebbieG

His example is missing an & before [txtqtr1].


| Fred tried you code and got syntax error message You may have entered an
| operand without an operator?
| Any ideas?
| Thanks
| Tony
| | > On Mon, 14 Feb 2005 17:26:01 +0000 (UTC), Tony Williams wrote:
| >
| > > Can anyone tell me why I'm not getting an answer to this in a calculated
| > > control
| > >
| =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]"="[txtqtr1]")
| > >
| > > tblmaintabs is my table and txtDomfacsoleqtr and txtMonthlabel are
| controls
| > > on a page on a tabcontrol on a form and txtqtr1 is a calculated control
| > > using
| > > =DateAdd("m",-3,[txtmonthlabel])
| > >
| > > I've tried it with [ ] brackets and without but I don't get any value
| shown.
| > >
| > > The form is based on a query which is based on my table tblmaintabs
| > > TIA
| > > Tony
| >
| > Your syntax is incorrect.
| > It depends upon the Datatype of the Field used as criteria.
| > What is the datatype of [txtMonthLabel]?
| > If it is a text datatype, you would write the where clause like this:
| >
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = '" &
| > [ControlNameOnform] & "'")
| >
| > If however, [txtMontLabel] is a Number datatype,then use:
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = " &
| > [ControlNameOnform])
| >
| > If your [txtMonthLabel] is a Date datatype, as it appears to be, then:
| >
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[CriteriaField] = #" &
| > [ControlNameOnform] & "#")
| >
| > So, using your field names, you want to use:
| > =DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel] = #"
| > [txtqtr1] & "#")
| >
| > --
| > Fred
| > Please only reply to this newsgroup.
| > I do not reply to personal email.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Summing a calculated control in a report 6
Converting query to VBA 4
Calculated control on a report 1
How can I make my select statement include two statements 3
Problem with subform showing too many records 4
Stopping #Error message with Dlookup formula 2
I Know it's Xmas BUT....... DLookup problem 6
Another DLookup problem 1

Top