Notation for greater than/less than when entering data?

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

Guest

I am importing data from excel files. I have ranges that contain dates, most
of which are simply numbers. However, I have a lot of dates that take the
form: >25,000 or <25,000. How can I retain this information when importing
into Access? As it stands now, the dates aren't imported at all...

Thank you,

Mike
 
I am importing data from excel files. I have ranges that contain dates, most
of which are simply numbers. However, I have a lot of dates that take the
form: >25,000 or <25,000. How can I retain this information when importing
into Access? As it stands now, the dates aren't imported at all...

Thank you,

Mike
Click to expand...

If you're importing into a Number or Date/TIme field, then the > and <
characters are simply not acceptable in those datatypes. Access has
stronger data typing than Excel; an Excel spreadsheet cell can
(basically) contain any type of data you wish, whereas an Access Field
can only contain data appropriate for its datatype.

To store such characters in Access, you must define the field as Text
(and then write code or design queries to convert the text string to a
number or date as needed).

John W. Vinson[MVP]
 
Sorry, I was being vague. The dates are calendrical years derived from
radiocarbon dates

I am looking for alternative ways of indicating that the value is greater
than or equal to the number in the field... As I have thousands of entries in
the Excel file, I was hoping that there was some notation that can be used to
indicate this. Not looking promising! I suppose the easiest thing to do would
be to remove the <> symbols altogether and retain the number.
 
I am looking for alternative ways of indicating that the value is greater
than or equal to the number in the field... As I have thousands of entries in
the Excel file, I was hoping that there was some notation that can be used to
indicate this. Not looking promising! I suppose the easiest thing to do would
be to remove the <> symbols altogether and retain the number.
Click to expand...

Consider using two fields - a one-character Text field containing
null, > or <; and a Number field. You could pretty easily split the
text strings by checking for the <> characters in a Query.

John W. Vinson[MVP]
 
Hi Mike,

You can either go with a text data type, as John Vinson suggested, or if you
want to use a numeric field (long integer suggested), you could possible add
another column that indicates the equality (ie. =, <, or >). I imagine with
carbon dating that there may not be too many cases where the = operator could
be used with any degree of certainty.

You could use an update query to update the value of a new column, based on
the equality symbol used. So for example, you might start by importing the
data into a field set as a text data type. Use an update query to update the
new equality field with the value calculated from:
Left$([YourTextDataTypeFieldName], 1).

Use the Mid$ function to update a numberic CalendricalYears field. Something
like this when viewing the query in SQL View:

UPDATE YourTableName SET
YourTableName.Equality = Left$([YourTextDataTypeFieldName],1),
YourTableName.CalendricalYears = Mid$([YourTextDataTypeFieldName],2);


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Thanks to both John and Tom - you've provided me with an answer that not only
works but is extremely simple and elegant - to paraphrase TH Huxley: how
stupid of me not to have thought of that!

A text column with =, <,> indicators solves my problem perfectly.

Thanks very much!
 

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

Similar Threads

Greater than, less than, AS WELL AS value=0 5
Sumif number is greater than a number but less than another number 2
Greater Than Symbols and calculations 0
Greater than, less than formula 3
IF statement greater than and less than 8
greater than / less than 11
Countif using less than or greater than criteria 3
Minimum Date Greater Than Observation Start Date 2

Back
Top