Access, how to convert data type from text to number

cindyl · October 4th, 2010, 08:28 AM

Hi everyone, I need your help!

On my Access table Projects0, my field name IndexCode, data type is text. I try to use combo box and List box. combo box for IndexCode0, and List box for ProJe. I need to bound these two fields. When I select item from IndexCode0, the related item should show up on List box Proje, but I got "data type mismatch in criteria expression". Here is my code:

****

Private Sub IndexCode0_AfterUpdate()
Me.ProJe.RowSource = "select ProjectName from Projects0 where IndexCode=" & Me.IndexCode0

End Sub

**

If I swithch IndexCode data type from text to number, it works, but I can not. Because in this field, many data start with 0, like 00723, so I have to use data type text for IndexCode on table.

Thanks,

Cindy

HiTechCoach · October 4th, 2010, 11:26 AM

Try:

Code:

Private Sub IndexCode0_AfterUpdate()

Me.ProJe.RowSource = "select ProjectName from Projects0 where IndexCode=" & Chr(34) & Me.IndexCode0 & Chr(34)

End Sub

cindyl · October 4th, 2010, 11:48 AM

Quote:

Originally Posted by HiTechCoach

Try:

Code:

Private Sub IndexCode0_AfterUpdate()

Me.ProJe.RowSource = "select ProjectName from Projects0 where IndexCode=" & Chr(34) & Me.IndexCode0 & Chr(34)

End Sub

Thank you so much, it works! You made my day!
Cindy

cindyl · October 4th, 2010, 11:50 AM

Thank you so much! You made my day!

Cindy

HiTechCoach · October 4th, 2010, 11:58 AM

You're welcome!

Glad I could assist.

I also have lots of stuff on my site @ http://www.hitechcoach.com