More Dlookup problems

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

Hello
I have the following Dlookup that I am using:

Sub Getdata()
varval = "=DLookUp(""[In_Instructions]"",""Instructions"",""[In_C_Type]='" &
"avg"" & ' & " And "& ""[In_P_Type]='" & "cname"")'"
Me.Instruction.Text = varval
End Sub

avg is a publically declared variable that equates to a text value
cname is a publically declared variable that equates to a text value
In_C_Type is a text field in the Instructions table
In_P_Type is a text field in the Instructions table
Me.Instruction.text is a memo field from my Instructions table. I have it
displayed on the form. I was going to use the "Controlsource" but kept
getting a syntax error when trying to use it.

I get a type mismatch when I try to run this. The type mismatch comes "in"
the dlookup function itself.

Any suggestions?
Thank you so much
Terry V
 
Not sure why; maybe somebody can explain it to me, but after hrs of playing
with this, this is what finally worked:

Private Sub getdata()
Dim varval
On Error Resume Next
Me.Instruction.Visible = True
Me.Instruction.Enabled = True
varval = DLookup("[Instruction]", "Instructions", _
"[In_C_Type] = avg " & "And " & "[In_P_Type] = cname")
Me.Instruction.SetFocus
Me.Instruction.Value = varval
End Sub

Thanks so much :)
Terry V
 
Terry,

I fail to see how your second attempt worked at all. Assuming avg is numeric
and cname is a string, I would have thought:
varval = DLookup("[Instruction]", "Instructions", "[In_C_Type] = " &
avg & "And [In_P_Type] = """ & cname & """")

It seems you were originally thinking that DLookup was a string value on its
own, so in essence what you were doing was setting the value of varval to
"=DLookup(........." and so on. Your second attempt treated DLookup as a
function, which it is. Also, in your first attempt, you tried to include
quotes incorrectly.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


Terry said:
Not sure why; maybe somebody can explain it to me, but after hrs of playing
with this, this is what finally worked:

Private Sub getdata()
Dim varval
On Error Resume Next
Me.Instruction.Visible = True
Me.Instruction.Enabled = True
varval = DLookup("[Instruction]", "Instructions", _
"[In_C_Type] = avg " & "And " & "[In_P_Type] = cname")
Me.Instruction.SetFocus
Me.Instruction.Value = varval
End Sub

Thanks so much :)
Terry V
Terry said:
Hello
I have the following Dlookup that I am using:

Sub Getdata()
varval =
Click to expand...
"=DLookUp(""[In_Instructions]"",""Instructions"",""[In_C_Type]='"
&
"avg"" & ' & " And "& ""[In_P_Type]='" & "cname"")'"
Me.Instruction.Text = varval
End Sub

avg is a publically declared variable that equates to a text value
cname is a publically declared variable that equates to a text value
In_C_Type is a text field in the Instructions table
In_P_Type is a text field in the Instructions table
Me.Instruction.text is a memo field from my Instructions table. I have it
displayed on the form. I was going to use the "Controlsource" but kept
getting a syntax error when trying to use it.

I get a type mismatch when I try to run this. The type mismatch comes "in"
the dlookup function itself.

Any suggestions?
Thank you so much
Terry V
Click to expand...
Click to expand...
 
Actually, avg is text. It comes from a variable that picks up the label
name of a checkbox.
But Im still not sure why it works. I looked at microsoft's site and got
some examples and this does not fit any of their examples.

Thank you so much
Terry V


Graham R Seach said:
Terry,

I fail to see how your second attempt worked at all. Assuming avg is numeric
and cname is a string, I would have thought:
varval = DLookup("[Instruction]", "Instructions", "[In_C_Type] = " &
avg & "And [In_P_Type] = """ & cname & """")

It seems you were originally thinking that DLookup was a string value on its
own, so in essence what you were doing was setting the value of varval to
"=DLookup(........." and so on. Your second attempt treated DLookup as a
function, which it is. Also, in your first attempt, you tried to include
quotes incorrectly.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


Terry said:
Not sure why; maybe somebody can explain it to me, but after hrs of playing
with this, this is what finally worked:

Private Sub getdata()
Dim varval
On Error Resume Next
Me.Instruction.Visible = True
Me.Instruction.Enabled = True
varval = DLookup("[Instruction]", "Instructions", _
"[In_C_Type] = avg " & "And " & "[In_P_Type] = cname")
Me.Instruction.SetFocus
Me.Instruction.Value = varval
End Sub

Thanks so much :)
Terry V
Terry said:
Hello
I have the following Dlookup that I am using:

Sub Getdata()
varval =
Click to expand...
"=DLookUp(""[In_Instructions]"",""Instructions"",""[In_C_Type]='"
&
"avg"" & ' & " And "& ""[In_P_Type]='" & "cname"")'"
Me.Instruction.Text = varval
End Sub

avg is a publically declared variable that equates to a text value
cname is a publically declared variable that equates to a text value
In_C_Type is a text field in the Instructions table
In_P_Type is a text field in the Instructions table
Me.Instruction.text is a memo field from my Instructions table. I
Click to expand...
Click to expand...
have
it
Click to expand...
Click to expand...
 
Terry,

If avg is text, then this is what you should be using:
varval = DLookup("[Instruction]", "Instructions", "[In_C_Type] = """ & avg
& """And [In_P_Type] = """ & cname & """")

<<But Im still not sure why it works.>>
I doubt it ever worked. Sometimes things appear to work, when they actualy
don't.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


Terry said:
Actually, avg is text. It comes from a variable that picks up the label
name of a checkbox.
But Im still not sure why it works. I looked at microsoft's site and got
some examples and this does not fit any of their examples.

Thank you so much
Terry V


Graham R Seach said:
Terry,

I fail to see how your second attempt worked at all. Assuming avg is numeric
and cname is a string, I would have thought:
varval = DLookup("[Instruction]", "Instructions", "[In_C_Type] = " &
avg & "And [In_P_Type] = """ & cname & """")

It seems you were originally thinking that DLookup was a string value on its
own, so in essence what you were doing was setting the value of varval to
"=DLookup(........." and so on. Your second attempt treated DLookup as a
function, which it is. Also, in your first attempt, you tried to include
quotes incorrectly.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


Terry said:
Not sure why; maybe somebody can explain it to me, but after hrs of playing
with this, this is what finally worked:

Private Sub getdata()
Dim varval
On Error Resume Next
Me.Instruction.Visible = True
Me.Instruction.Enabled = True
varval = DLookup("[Instruction]", "Instructions", _
"[In_C_Type] = avg " & "And " & "[In_P_Type] = cname")
Me.Instruction.SetFocus
Me.Instruction.Value = varval
End Sub

Thanks so much :)
Terry V
Hello
I have the following Dlookup that I am using:

Sub Getdata()
varval = "=DLookUp(""[In_Instructions]"",""Instructions"",""[In_C_Type]='"
&
"avg"" & ' & " And "& ""[In_P_Type]='" & "cname"")'"
Me.Instruction.Text = varval
End Sub

avg is a publically declared variable that equates to a text value
cname is a publically declared variable that equates to a text value
In_C_Type is a text field in the Instructions table
In_P_Type is a text field in the Instructions table
Me.Instruction.text is a memo field from my Instructions table. I
Click to expand...
have
it
displayed on the form. I was going to use the "Controlsource" but kept
getting a syntax error when trying to use it.

I get a type mismatch when I try to run this. The type mismatch comes
"in"
the dlookup function itself.

Any suggestions?
Thank you so much
Terry V
Click to expand...
Click to expand...
Click to expand...
 
Graham
Thank you :)
Im not sure why it was working with what I had, but hey.... stranger things
have happened.
I decided to go ahead with what you posted for me... that way I know it
stands a better chance of not crashing on me.

Thank you so much
Terry


Graham R Seach said:
Terry,

If avg is text, then this is what you should be using:
varval = DLookup("[Instruction]", "Instructions", "[In_C_Type] = """ & avg
& """And [In_P_Type] = """ & cname & """")

<<But Im still not sure why it works.>>
I doubt it ever worked. Sometimes things appear to work, when they actualy
don't.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


Terry said:
Actually, avg is text. It comes from a variable that picks up the label
name of a checkbox.
But Im still not sure why it works. I looked at microsoft's site and got
some examples and this does not fit any of their examples.

Thank you so much
Terry V


Graham R Seach said:
Terry,

I fail to see how your second attempt worked at all. Assuming avg is numeric
and cname is a string, I would have thought:
varval = DLookup("[Instruction]", "Instructions", "[In_C_Type] =
Click to expand...
Click to expand...
"
&
avg & "And [In_P_Type] = """ & cname & """")

It seems you were originally thinking that DLookup was a string value
Click to expand...
on
its
own, so in essence what you were doing was setting the value of varval to
"=DLookup(........." and so on. Your second attempt treated DLookup as a
function, which it is. Also, in your first attempt, you tried to include
quotes incorrectly.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


Not sure why; maybe somebody can explain it to me, but after hrs of
playing
with this, this is what finally worked:

Private Sub getdata()
Dim varval
On Error Resume Next
Me.Instruction.Visible = True
Me.Instruction.Enabled = True
varval = DLookup("[Instruction]", "Instructions", _
"[In_C_Type] = avg " & "And " & "[In_P_Type] = cname")
Me.Instruction.SetFocus
Me.Instruction.Value = varval
End Sub

Thanks so much :)
Terry V
Hello
I have the following Dlookup that I am using:

Sub Getdata()
varval =
"=DLookUp(""[In_Instructions]"",""Instructions"",""[In_C_Type]='"
&
"avg"" & ' & " And "& ""[In_P_Type]='" & "cname"")'"
Me.Instruction.Text = varval
End Sub

avg is a publically declared variable that equates to a text value
cname is a publically declared variable that equates to a text value
In_C_Type is a text field in the Instructions table
In_P_Type is a text field in the Instructions table
Me.Instruction.text is a memo field from my Instructions table. I have
it
displayed on the form. I was going to use the "Controlsource" but kept
getting a syntax error when trying to use it.

I get a type mismatch when I try to run this. The type mismatch comes
"in"
the dlookup function itself.

Any suggestions?
Thank you so much
Terry V
Click to expand...
Click to expand...
Click to expand...
 
Back
Top