Problems with a query; result to textbox.

[Thuman]

Hi guys,
I'm losing hair over this, so i decided to cry for help here
Right, i have a query that, when fired off from the saved queries
works like a charm, it's as follows:
SELECT [ansvarshvn]
FROM ansvarshavende
WHERE Forms![Registreringer 2007].afdeling Between
left([afdeling],instr(1,[afdeling],"-")-1) And
right([afdeling],instr(1,[afdeling],"-")-1);
This returns a single value as intended, using the texbox control on
the form called Registreringer 2007 as variable for the BETWEEN
clause.
Now, the result from this query i want to insert into the textbox
called "ansvar"
I've tried to do this in a hundred ways soon, but with no luck.. I
always end up with a Type Mismatch, error 13.
I've tried:
Private Sub medarbejderid_AfterUpdate()
ansvar.Text = Dlookup("[ansvarshvn]","ansvarshavende","Forms!
[Registreringer 2007].afdeling Between left([afdeling],instr(1,
[afdeling],"-")-1) And right([afdeling],instr(1,[afdeling],"-")-1)")
End Sub
result was Type Mismatch ;(
-----
I also tried this approach, but got the same result.
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
strSQL = "SELECT ansvarshvn FROM [ansvarshavende] WHERE Forms!
[Registreringer 2007].afdeling Between left([afdeling],instr(1,
[afdeling]," - ")-1) And right([afdeling],instr(1,[afdeling]," -
")-1);"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
ansvar.Text = rs!ansvarshvn
Set rs = Nothing
Set db = Nothing
---
The table called Afdeling is a text column with records like this:
"340-580"
"581-637"
"638-900" etc..
Any hints?

 

[John Spencer]

One: The text property of a control is only available while the control has
the focus. Use the Value property (or just refer to the control since the
value property is the default property)

Two: If you need to include quotes in a string, you need to double them up.

Private Sub medarbejderid_AfterUpdate()

ansvar= Dlookup("[ansvarshvn]","ansvarshavende","Forms![Registreringer
2007]!afdeling Between left([afdeling],instr(1,[afdeling],""-"")-1) And
right([afdeling],instr(1,[afdeling],""-"")-1)")

End Sub

That may or may not work for you. Of course a better table design might be to
have afdeling split into two fields- the start of the range and the end of the
range. That way you wouldn't need to parse out the separate portions of the
field for the comparison.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Thuman]

Argh! So simple..

Thanks

And yes, the simpler table design would be the way to go for sure