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?

If you open the table in design view, what kind of field is afdeling?

And what size?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Thuman" <brianm (AT) thumann (DOT) dk> wrote