By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,842 Members | 2,186 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,842 IT Pros & Developers. It's quick & easy.

Multiple formats in query

P: n/a
Hello, I've been asked to help someone set up a database. One of the
fields in the database is a text field that holds data imported in this
format:

11-A-11-11

The users prefer to see the information in the hyphenated format
because it is easier for them to read/visualize/whatever. (Of course,
it doesn't have to be stored in that format to be presented in that
format.)

However, the users would like to query using either of these formats in
the "Please enter number" box:

11-A-11-11
11A1111

I do not know how to do this. Do I need to use more than one query?
Should I change how the data is stored? Can any of you point me in the
right direction?

Thanks very much!

Chris

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
If you store the data without the dashes, then if you also strip dashes from
the search value string bebore using it in the query, it should always work,
right?

On 11 Nov 2005 13:49:01 -0800, so*************@yahoo.com wrote:
Hello, I've been asked to help someone set up a database. One of the
fields in the database is a text field that holds data imported in this
format:

11-A-11-11

The users prefer to see the information in the hyphenated format
because it is easier for them to read/visualize/whatever. (Of course,
it doesn't have to be stored in that format to be presented in that
format.)

However, the users would like to query using either of these formats in
the "Please enter number" box:

11-A-11-11
11A1111

I do not know how to do this. Do I need to use more than one query?
Should I change how the data is stored? Can any of you point me in the
right direction?

Thanks very much!

Chris


Nov 13 '05 #2

P: n/a
The Input Mask property (string) of a text control can allow you to indicate
that formatting elements (such as dashes) not be stored in the underlying
database.
I recommend you do *not* store the hyphens in the database since a) it's
unnecessary, and b) there's no turning back if you do.

Based on the format you have described, an input mask that would appear to
match that is:
">00\-L\-00\-00;;_"

An input mask has 3 fields separated by semicolons.
The middle (second) field indicates whether or not to store the formatting
(display) elements literally in the stored string. I have left it blank to
stop that happenning.
The third field has a "_" which is the place holder for blanks spaces where
characters need to be input.

Find out more about what the characters in the first field mean by pressing
F1 on the Input Mask property of the text box.

You can test the above mask by pasting it (without the quotes) into the
Input Mask property of a text box.

Ian.

<so*************@yahoo.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
Hello, I've been asked to help someone set up a database. One of the
fields in the database is a text field that holds data imported in this
format:

11-A-11-11

The users prefer to see the information in the hyphenated format
because it is easier for them to read/visualize/whatever. (Of course,
it doesn't have to be stored in that format to be presented in that
format.)

However, the users would like to query using either of these formats in
the "Please enter number" box:

11-A-11-11
11A1111

I do not know how to do this. Do I need to use more than one query?
Should I change how the data is stored? Can any of you point me in the
right direction?

Thanks very much!

Chris

Nov 13 '05 #3

P: n/a
On Fri, 11 Nov 2005 22:51:35 GMT, "Ian Hinson" <pp******@bigpond.net.au>
wrote:
The Input Mask property (string) of a text control can allow you to indicate
that formatting elements (such as dashes) not be stored in the underlying
database.
I recommend you do *not* store the hyphens in the database since a) it's
unnecessary, and b) there's no turning back if you do.


I agree on the reason you're saying to use input masks, but disagreethat using
input masks is ever a good thing.

Input masks are very counterintuitive to use, particularly when clicking on a
control, since you end up selecting a character position rather than the whole
field.

What I would do (in spite of the extra effort) is set the control format to
@@-@-@@-@@, so it displays right, then use Before/After update events as
below. It's not perfect, because the format always reverts to condensed when
editing the field, but I suspect there's also some solution to that using
Form_Change, or something if necessary.

Private Function MyValueCondensed() As Variant
Dim strCondensed As String
strCondensed = Replace(Me!txtMyValue & "", "-", "")

MyValueCondensed = IIf(strCondensed = "", _
Null, strCondensed)
End Function

Private Sub txtMyValue_BeforeUpdate(Cancel As Integer)

If IsNull(MyValueCondensed()) Then Exit Sub
If Not (MyValueCondensed() Like "##[a-zA-Z]####") Then
Cancel = True
MsgBox "Data entered here must be like 11-A-22-33 or like 11A2233."
'(a little white lie since we'd be just as happy with 1-1A2-233)
End If

End Sub

Private Sub txtMyValue_AfterUpdate()
Me!txtMyValue = MyValueCondensed()
End Sub
Nov 13 '05 #4

P: n/a
Thanks Ian ... That's a perfect mask ... I stripped the hyphens from
the data in the tables and then applied the mask. But hyphenated
queries do not work.

Unless I'm still doing something wrong, which is entirely possible ...
Is it possible to put a mask on the query value, so that the search
value is forced into the proper format?

Thanks again.

Nov 14 '05 #5

P: n/a
Wow, thanks for the code!

I am trying it out but my Access expertise is limited to simple queries
& tables. So this might take me awhile ...

Nov 14 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.