String And Numeric Data Comparison

Apr 19, 2006

I need to make a query to compare equality of two pieces of data, one is a 6 character fixed-length text string (where the first character can be ignored) and the other is a 4 or 5 digit number (long integer, if 4 digit number can be assumed to be 5 with a leading 0).

I do not have the ability to change the way I receive the data, they are on linked tables of different customers.

I tried using various SQL functions to convert the data in the query but they don't seem to work in Access, so I am getting "Type incorrect in the expression" errors.

How do I do this in ACCESS? I would prefer a SQL query solution to a VBA solution if possible, the data is updated VERY often and the application is already doing too much processing on the form_timer() event.

View Replies


ADVERTISEMENT

Queries :: Numeric Comparison And Null Values

Sep 24, 2013

I have a form with a textbox, where one inputs a number and then I run a query with the form criteria. The query is

Code:
Select Blah blah from dbo_temp where A>textbox value OR B>textbox value OR C>textbox value OR D>textbox value

The columns which are linked to the textbox some times contain null values. So, A, B, C, D columns do contain null values.

The expression in the "Criteria" column of Columns A, B, C, D (all on different lines - to make sure OR criteria is fullfilled) is as follows

Code:
>IIf(IsNull([Forms]![MainForm]![Criteria]),-100,[Forms]![MainForm]![Criteria])

I've put in -100 as an arbitrary never possible number. Obviously, this does not return Null values.

Question: How can I return both Null & Numbers when the Textbox in the form is left blank? in all the columns. Currently, I am not getting Null values

Code:
>IIf(IsNull([Forms]![MainForm]![Criteria]),SHOW ME EVERYTHING INCLUDING NULL VALUES & NON-NULL NUMBERS,[Forms]![Material Finder]![txtPS])

or in other words

If the textbox is blank, show me all the data available, else if it is not blank then show me only the values that are greater than the number entered in the textbox from within column A, B, C, D ....

View 3 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

General :: Extract Text And Numeric From A String

Feb 12, 2013

"Att Mr/Mrs Vezi Your DEFAULTED account has been handed over to XXX. Payment DUE IMMEDIATELY Ref 1315519509. Tel 0009377500"

I need to extract the word "Ref " plus the number that follows it from the above string. The problem is that the word "Ref " is not always in the same place and the number following it is not always the same length. (ie it could look like "Ref 123456 " or "Ref 1234567 ") The only commonality between the records is that the number will always follow the word "Ref ".

View 1 Replies View Related

Modules & VBA :: Check If String Contains Numeric Characters?

Nov 7, 2013

Is there an easy way in VBA to loop through a recordset and determine if the text string in a text field includes numeric characters?

I'm trying to do something like this:

Code:
Do Until rst.EOF
rst.Edit
If Left(rst!FldText,10) contains any numeric characters Then
rst!FldType = "Mixed"
ElseIF Left(rst!FldText,10) contains "PO" Then
rst!FldType = "PO"
Else
rst!FldType = "Std"
End If
rst.Update

View 2 Replies View Related

Reports :: Comparison Variable From Data Field

Mar 6, 2013

I need to have the comparison variable (< or >) to be read from a field in the form.
Namely:

WHERE (((([PackagesCosts].[TotalCost]) & [Forms]![F_Reports]![Increased] & [C].[TotalCost])

Where [Forms]![F_Reports]![Increased] is the filed on my form.

What is wrong in the syntax (as it is not working properly)?

View 13 Replies View Related

Reports :: Chart For Annual Comparison Of Data By Months

May 18, 2014

I have a small clinic database. I've got tblAppointments to show AppointmentID and AppointmentDate among various other data, but only the dates matter for what I'm trying to achieve.

I'm trying to show a chart on a report that shows the number of appointments by months for this year and previous year. How can I do this...without using SQL, hopefully?

Here's what I tried: I made a cross-tab query to successfully show the years 2013 and 2014 in the rows, months in the columns, and number of appointments as values. But then, I didn't know how to graph it to compare the number of appointments for the two years by months.

View 5 Replies View Related

Use A Query To Do A Running Total & Comparison With Weekly Data Points

Apr 13, 2006

I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:

View 3 Replies View Related

Queries :: How To Retrieve Only Numeric Data From F1 And Display That Data In A Field

Oct 1, 2013

Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.

Questions
How can i in Q1 retrive only numeric data from F1 and display that data i a field?
How can i in Q1 retrive only text from F1 and display that data i a field?

View 3 Replies View Related

Forms :: Sort Numeric In Alpha-numeric Text

Mar 11, 2014

I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.

The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.

Code for automatic number generation is:

Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _

[Code]....

My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?

View 2 Replies View Related

Numeric Data Types

Jan 17, 2006

Hi,

Strange but true.
I am trying to numeric data types on my access 2000 where I could input values in table like 0.3567 or 2.3454
Anyway any values of this kind with 4 decimal places. however somehow the values always get rounded to number + 4 zeros as of example:
if I put 0.756769 I want it to be as 0.7568 but I get 1.0000
I don't understand this. Can anyone help please?
I have not used access for ages and I seemed to be forgeting all this stuff.

Thanks

dfuas

View 2 Replies View Related

Formatting Numeric Data

Jan 29, 2008

I hope someone can help me with this issue. Currently I have numeric data that I need to convert. For example if a quantity is 42.5 in my data I need to format it to look like 00000042500 similarly if the quantity was -42.5 I need to format it to look like 00000042500-. Any assistance anyone can provided would be greatly appreciated.

View 5 Replies View Related

Formatting Numeric Data As Text

Mar 14, 2007

Access 97 SR1

I am importing a file that has two numeric fields in it. I was asked to import as fixed length text fields with leading zeroes and no decimal point.

I am supposed to export the data in Csv format.

Source field from IBM iSeries DB: quantity is 15,3. Quantity source value is 9, requested text format of five characters 00009, the Csv result is 9. The field in Access is defined as Text 5 characters.

Source field from IBM iSeries DB: price is 17,4. Price source value is 1020.0000, requested text format of nine characters 000001020, the Csv result is 1200.00. The field in Access is defined as Text 9 characters.


Thanks,

Dave

View 1 Replies View Related

General :: How To Check Numeric Input Data

Jul 17, 2015

I have an input box and wanted user just to input 7 digital data. However, I am afraid user may enter string data. Is there any way to check and ensure user can only input 7 digital data to that input box?

View 2 Replies View Related

Modules & VBA :: Putting Data Together With Auto-numeric Field?

Jan 15, 2015

I have 2 databases with the same structure, but different data.

I want to put all the data in 1 database.

The problem is that the most important table (clients)has an autonumeric id field, that is used to link the records to the fields of many other tables; so, I cannot delete or change that field without losing connections.

View 4 Replies View Related

General :: Excel Numeric Data To Be Imported As Text In MS Access

Aug 1, 2015

Attached in the ZIPPED file is an Excel spreadsheet.

Columns A is all numeric, and needs to be represented in access as a text field.
Column B is a mixed format of dates entered and in some instances only plain numeric. I need to import this column as is into a text field in access.

I tried importing the excel sheet, but the data gets changed.I tried to linked the Excel sheet but it also had an influence on the data.In both cases the influence of change is NOT throughout. Hence my need to get this spreadsheet into access as is.

View 7 Replies View Related

Modules & VBA :: Convert JSon Data String To Readable Data

Jun 2, 2014

thos is f.i. a json data sting :

{"vip_kaarten":"0","reg_kaarten":"0","extra_vip_ka arten":"0","bedrag_extra_vip_kaarten":"0.00","extr a_reg_kaarten":"0","bedrag_extra_reg_kaarten":"0.0 0","vrjr_kaarten":"2","extra_vrjr_kaarten":"0","be drag_extra_vrjr_kaarten":"0.00","website_link_spon sorpagina":"1","website_banner":"","social_extra_m ededelingen":""}

How can i convert this to readle data?First string is the field name, second the value.

View 4 Replies View Related

Table Comparison

Feb 26, 2006

Is there a way to look at two tables to compare records and add missing records from table to the other?

Thanks!
Terry

View 3 Replies View Related

Comparison Operators.

Apr 27, 2006

I'm having difficulty coming up with a way to exlcuded a group of dates from my Query. I am trying to have a set of dates come up without the dates, between 7/1/2006 and 7/31/2006.

I've tried <> Between #7/1/2006# And #7/31/2006#, and a few others with quotes and such. Any help would be greatly appreciated.

View 1 Replies View Related

Date Comparison

Jun 13, 2005

Hey all,

I've got to show records from a table that are 45 days old or newer.. im not quite sure how to do this in SQL ...

Select field from table where date > (todaysdate-45).. as you can see..clueless lol any help is _greatly_ appreciated

View 3 Replies View Related

Extracting Data From A Table As String

Nov 15, 2005

I need to be able to extract individual fields from a table as strings in order to then send this data to my EPOS printer (which will accept only strings to print) I have created the table with a query so the question could equally be how can i output strings as query results. Im using Access97 HELP!!
I have tried to set up a ADODB.Connection but either Access 97 wont accept it or i've got the coding wrong

View 3 Replies View Related

Picking Of Single Data Out Of A String Of It..

Jul 31, 2007

hi,

I am trying to pick out the ERAP no.s out of a whole string of data. ERAP no.s are like invoice no.s. Do I Append or Make table? How do i go about doing this? I want the ERAP no. in a row next to the the description field all next to its respective string of data too....


for an example..

1)ERAP43463 STAFF TEAM LUNCH MID-YEAR REVIEW

2) 0507 SUBMITTED ITEMIZED ACCR-ERAP43159

See.. the part where the ERAP no. appears is inconsistant.. if not i could simply use excel and use text to columns. We have thousands of lines like this every week... There has to be a way to pick out just this detail and fill in the column next to the respective datastring...

Can someone please advice me on how i can go about doing this?

View 2 Replies View Related

Text To Date Comparison

Jan 24, 2007

Hi all- I'm creating an update query.I have a field [dateLastEdit] that is stored as text, I have to compare it to a date entered on a form by user [date_from] (I need to select all dates greater than this) but I'm running into problems:when I use date_from in text or date format and compare it to dateLastEdit in text format the comparison is made in numeric order(so 12/02/2006 is selected as well as 12/02/2005 when date_from is 12/01/2006)SELECT PTSData.LastEditDateFROM PTSDataWHERE (((PTSData.LastEditDate)>=[Forms]![chMgmt_frm]![date_from]));when I use date_from in date format and compare it to dateLastEdit converted to date format I get "expression typed incorrectly or it is too complex to be evaluated" msg.SELECT DateValue([LastEditDate]) AS Expr1FROM PTSDataWHERE (((DateValue([LastEditDate]))>=[Forms]![chMgmt_frm]![date_from]));[LastEditDate]'s format is always mm/dd/yyyy hh:mm:ss, but as text, not date data type.I've tried CDate with same results, isolating it in its own query to test it, using hardwired dates as criteria, but no joy, am I missing something obvious?forgot to add- I've used all the above with left also to select only date portion with same results

View 5 Replies View Related

Values Comparison Query

Nov 20, 2007

I really have looked everywhere

I have 2 fields and I want a query to pull out the records with a lower or equal to quantity value than the stock.

ReorderValue
3
5

Quantity
2
6

As you can see it shuold pick out the top record because its lower, I just need a way of telling it to look at each records Reorder Value

View 8 Replies View Related

DateAdd / Comparison A97 Problem

Feb 8, 2008

I'm running a query to find records over one year old. When I use Date()-"365" or DateAdd (365 days, 12 months, 4 quarters, or 1 year) the records from one year ago today come up just like they are suppose to. The problem comes in when I put the comparison operator out front.

If I put < in front of either Date() or DateAdd minus one year, I get all records from yesterday and earlier as if it is ignoring the year and only looking at the month and day. If I put > in front, I get today's records.

Date() and DateAdd by themselves are working as advertised. Things only get weird when I put < or > out front.

What am I doing wrong?

Thanks,
Forrest

View 2 Replies View Related

Complex Query And Comparison

Aug 9, 2005

I have a scheduling database that my company uses to manage our customers deliveries. We would like to make this database more intelligent by adding some logic and I am not quite sure how to proceed.

On the form frmDelivery, the user inputs all the header information for the delivery, in the subform subfrmLotInfo, they input the houses that will be delivered, the delivery date, and some other miscellaneous information.

When the user inputs the delivery date, the system should count back a number of days based on other criteria and set a build date (when we should start building the cabinets, typically 5 days before the delivery date). Then I need the database to run a query against this date and determine how many boxes we have scheduled to be built already on that day, if it exceed a certain number than I need to it to search an available date close to that date that has not exceeded our production capacity and schedule the lot for that date.

This needs to happen as well if the user changes the delivery date at a later time. Our customers change delivery dates constantly. This will help us schedule our manufacturing plant better and reduce the possiblity of scheduling more than we can actually accomodate.

I have a table tblPrdCapacity that holds how many boxes we can build on a given day. It hold Date, PrdCap (number of boxes). In the subfrmLotInfo, the field BoxesBuilt tells how many boxes will be built for that Lot.

Any info would be greatly appreciated!! I have been tasked to accomplish this as soon as possible.

Thank you!!!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved