Data type question

P

PC

Hi,

I created a db some time ago which allows users record incoming invoices and
Direct Debit payments. the Db works fine but now there is a requirement to
create a report which lists transactions between 2 specified reference
numbers.

The reference numbers look like 01/06, 02/06, ... etc (i.e. Transaction
Number/Month of transaction) and the data type currently is "Text".
Obviously I can't create a "between" query on these.

Can anybody suggest some way I can format this field or some code to use to
allow me to specify between 2 Transaction Reference Numbers as the criteria
for the query

Thanks in advance
 
J

Jeff Boyce

A first suggestion would be to modify the database structure and eliminate
storing two facts in one field. If you have two facts (i.e., month of
transaction, sequence number of transaction), record those in/as two fields.
(and by the way, how will you separate out the Month=6 transactions for
2003, 2004, 2005, ...?!)

In the short term, take a look at the Left(), Mid() and Right() functions,
which give you a way to build a query and "get" the month and the sequence
numbers as separate fields. If you build a query that separates out these
two facts, you can then sort by those.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top