pick up characters to run query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
Damian said:
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.
:
Click to expand...

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.
 
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/
 
Back
Top