pick up characters to run query

[Guest]

I have a column called ‘B/ref’; this is a reference to identify invoices that
are stored in the Database. The format of this is Oct-01, Oct-02, Oct-03 etc.
And if it was the month of August the reference would be Aug-01, Aug-02
Aug-03, Aug-04, Aug-05 etc.

Is there a way where I can run a Query to show everything that is only in
Jan, only in Feb, only in Mar etc. I have tried running a parameter say
between Oct-01 to Oct-44 but it does not pick up everything. I want to be
able to run a query and type in the first 3 letters of that month to show all
the invoices’ for that month.

 

[Guest]

Hi Kirt84,

Create a query and have that query return all the fields you want, then have
a field like this:

Restrict: left([B/ref], 3)

with criteria [Enter the 3 letter Month Code]

The query when you run it will prompt the user for the 3 letter month code
and then compare that to the left 3 chars of the B/ref field, returning those
that match.

Hope this helps.

Damian.

 

[Joseph Meehan]

Hi Kirt84,

Create a query and have that query return all the fields you want,
then have a field like this:

Restrict: left([B/ref], 3)

with criteria [Enter the 3 letter Month Code]

The query when you run it will prompt the user for the 3 letter month
code and then compare that to the left 3 chars of the B/ref field,
returning those that match.

Hope this helps.

Damian.
:

For the record, you might want to consider splitting that data. The
more proper way of handling that would be a to have two fields Month (three
character text) and increment (number). Then combine them for display
reasons.

What do you do from year to year? Without a year indictor, it appears
you are going to delete data at the end of the year or confuse data. I
would suggest that a third year field might be in order. You can hide that
field if you like and even set it up to display only the current years data
to the user.

 

[Jeff Boyce]

Are you saying that you have a single field into which you enter a month
abbreviation AND a sequence number? Two facts in one field is a formula for
headaches (as you are finding!).

Instead, if you want to know Month and SequenceNumber, you could use two
fields -- one to store a date (e.g., October 23, 2006) and one to store a
sequence number (e.g. 17). Then you'd simply use a query to return the
month portion of the date, concatenated with a dash ("-") and the sequence
number.

What you store and what you display don't have to be exactly the same...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/