General :: Parse Substring From Barcode With Variable Digit Count

Aug 23, 2012

I need to identify and parse (substring) a number of digits from a barcode. The barcode digit count will vary, based on the number of details included in the barcode.

For example: the barcode for GS1 DataBar begins with a 4 digit code of 8110 identifying the code as GS1. The 5th digit then identifies the number of digits directly following which constitute the manufacturers code, which can range anywhere from 6 digits to 12 digits. I need to pull the number of digits out of the barcode based on the value of the 5th digit placeholder and pull only that many digits from the string and replace them into another field. There are potentially 70 total digits in the barcode. I have only provided the first section, which I am asking the question on.

Simply put: I need to tell the substring to identify the number to parse from digit 5, (which will have a min of 6 digits and a max of 12)

How do I pull the information out of a string to create a substring in another field by first defining the number of digits to pull out (Digits 6 to 17 min of 6 digits and a max of 12 digits) based on a value within the string (digit 5= value)

So if Digit 5 is 6 the returned value would be: Digits: 6 7 8 9 10 11 (6 6 6 6 6 6 )

If Digit 6 is 9 the returned value would be: Digits: 6 7 8 9 10 11 12 13 14 (9 9 9 9 9 9 9 9 9)

The first portion of the barcode is below: There are other pieces necessary to be pulled the same way, but from other sections, and based on yet another X desinator field (the next with a min of 1 and max of 5) I assume the pull would be similarly completed.

I don't know how to write code, I have used formulas but not this detailed or specific.

Ex: I know = Min (barcode, 5, 6) would pull the 6 digits after the 5 spot, but how do I vary the 6 (count) number in the formula?

Example below: No additional spaces, dashes, letters etc. (A's = 1-4, X = 5, M's = 6-17)

A A A A X M M M M M M M M M M M M

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

View Replies


ADVERTISEMENT

Forms :: Put Barcode In Field When Reader Scans Barcode / It Records Current Time

Nov 11, 2013

I want to be able to put a barcode in a field that when a reader scans the barcode it records the current time. Need it for access control to record who is in and out of the office. Reading other threads I understand it may be possible in Dlookup.

View 3 Replies View Related

Reports :: Barcode Font - Generate Number As Barcode On Report Records

Jun 8, 2013

I am using a barcode font in order to generate a number as a barcode on my report records. In order for my barcode scanner to read the barcode it needs an asterisk at the beginning and at the end.

So, if my record ID is 62 - in order for the barcode to be displayed correctly, it needs to be on the report as *62* .

Without digressing into a discussion on barcode methods in Access, how can I precede and succeed each ID number field with an asterisks?

View 4 Replies View Related

General :: Triggering And Reading Multiple Barcode Readers

Nov 25, 2014

I will have a PC and a number of scanners in a production line. I will need at least 15-20 scanners and would like to plan for more if needed. When an operator clicks on a button I would like to have each of the scanners triggered and return the value. They can be done in turn or all at once (preferred for speed). I need to know which value came from each scanner. I would then compare the barcode read at each station versus what is supposed to be at that station and either pass or fail.

View 2 Replies View Related

General :: Print Barcode To Be Used For Data Input Into Another System

Aug 7, 2014

I'm trying to print a barcode to be used for data input into another system.

The data contains alpha number and some special characters like space, underscore, / and &

I want the form to print the bar code

128 and 3of9 don't handle this data.

View 5 Replies View Related

General :: How To Scan Product Barcode Then Interpret It Into Data

Jul 25, 2013

I'm trying to make a database for some product that have barcode, but i don't understand how to scan product barcode then interpret it into data in access,,, and how later I can look for these data by using barcode.

View 4 Replies View Related

General :: Text Box Event Coding - Jump To Next Field After Using Barcode Scanner

May 20, 2015

I am creating a inventory database for work. I am trying to get the cursor to jump to the next text box for data entry using a barcode scanner. I have a limit on how many characters there can be in this field but yet even after it is all filled the cursor stays in that same field. I am fairly new to access, especially in code building. Do I use "After Update" under "Event" in the "Property Sheet"?

Capture.PNG
Capture1.PNG
Capture2.PNG
Capture3.PNG

View 4 Replies View Related

Modules & VBA :: Count Of A Variable Range

Jul 30, 2015

1. I have a sample database table in excel where depending on column 2 value >1 is checked and logical results by IF check placed in Column D. The values having "Y" in column D are placed in Columns E:F.

2. Now I want to place count of values meeting the condition on last cell of the filled E:F range.

3. VBA code written by me proceeds fine and places values meeting condition in column E:F

4. I am beginner to VBA and finding difficulty in proper code for placing sum of counts of acceptable values in range F2:F7 in cell F8 ie last value in the array. Presently I have placed the value by In-built Count function in Excel.

File Count on Macro from another table is attached.

View 1 Replies View Related

Trying To Update Rows Based On A Count Variable

Sep 1, 2006

Apologies if this is a basic question. Here's my situation:

I'm trying to update rows in a table based on a count of items in the same table. The table in question contains order line items. On each line item, I'd like to store the total number of items attached to that order (because it affects how the individual line items are processed).

I have a query that seems like it should do the trick, but Access doesn't like it:

UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");

Here's the error I get:

Operation must use an updateable query.

Thoughts?

View 5 Replies View Related

Modules & VBA :: Extract Variable Count Of Columns And Transpose It To Another Table

Mar 14, 2014

I wanna extract a variable count of columns and transpose it to another table.

My source table is called FC containing columns like 1 FC, 2 FC .., 12 FC

My target table is called Forecast_Quantities

Tried the following:

Code:
Public Sub TRANSPOSE()
Dim rs As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim varItm As Variant
Dim I As Integer
Set rs = CurrentDb.OpenRecordset("FC")

[Code] ....

Somehow it doesn't recognize the I FC column in the table FC.

View 1 Replies View Related

Extract Substring

Mar 31, 2006

I have a table in which one field badly needs separating into two. Currently, it is laid out as a mixed case text string followed by an upper case text string, e.g. O little town of Bethlehem FOREST GREEN.

I need to move the upper case string (FOREST GREEN) to a new field (and then trim the original field accordingly). Since there is no delimiter between the two parts of the field, I have no idea how to proceed. Can anyone help?

Thanks.

View 2 Replies View Related

Extracting A Substring

Mar 1, 2007

Hi Everybody,
I have an access database and in a certain table I have a string field which has two values. I want to make a query that will show all the fields which contain a substring.

I'd better give an example:

Table: Kid

Fields:
Name: Ben
Activities: Soccer, Tennis

Name: Dan
Activities: Soccer

Name: John
Activities: Football, Origami


.
.
.

I want to show in a query all the Names which have a "Soccer" substring in their Activities string field

Need your help ASAP !!!

View 3 Replies View Related

Substring Out Of Range

Aug 21, 2005

I've walked through this in debug over and over and I just don't get why I keep getting "substring out of range" error at the statement

If (CCur(Fields(28))<>0) Then

I've attached the bmp to show the code (hopefully I uploaded it correctly)

View 1 Replies View Related

Simple Substring Query

Mar 26, 2006

I'm a rank beginner in Access, and apologize in advance for such an elementary question, but I need the basic syntax to run a Delete Query on the substring of the first character in a field. The question is #8 in Shelly Cashman's Office 2003 Course One p.AC168, Delete all records where Description starts with S.

p.AC137 deals with Delete Query, but only covers deletions based on whole fields. I tried 'Help', but ended up at a Microsoft site offering me a 30MB file on SQL. I may well get deeper into Access after a more advanced course, but this one is for beginners.

TIA for any help with this.

~~~Peter.

PS I thought I created a profile with an alias, but I seem to have ended up with pvmoor@verizon. instead. Oh, well.

View 5 Replies View Related

Access Substring Or Trim

Jun 15, 2007

I use this SQL in another query platform, but how can I do it in an access query object?

SUBSTRING (AL1.TRAIN_ID FROM 1 FOR 3)


Which cuts my 7 length string down to 3 char.

View 2 Replies View Related

Queries :: Substring Extraction - Retrieve Information From Field

Aug 7, 2014

How I can retrieve the following information from a field

[CONTENTS]
1 x 17.7ml
2 x 17ml
1 x 181.1ml

I need
17.7
17
181.1

I was using a combination of CAST(LEFT(RIGHT...)) AS Float

but that obviously won't work due to too many variations...

View 4 Replies View Related

Modules & VBA :: Change Color Of Substring In Rich Text Field

Aug 14, 2013

I'm using Access 2010. I'm passing a string into the OpenArgs of my report - works fine. In the report there are 3 rich text fields which may contain the text I passed in, and if so I want to change the color of that text to red so it stands out.

The value passed to the report changes so I'll need to use VBA in the detail's format section to check each of the 3 rich text fields.

View 5 Replies View Related

General :: SQL Statement Using A Variable

Feb 25, 2014

Is there a way to use a variable in an SQL statement?

Something like this:

Code:

dim mysql1, newname
newname = inputbox("direction on what to do go here?",,)
mysql1 = "INSERT INTO FinalAll SELECT newname.* FROM newname;"
DoCmd.RunSQL mysql1

Where newname would be a variable assigned from an input box.

I want to populate a single table from several different tables but have the user specify which tables to use.

View 8 Replies View Related

General :: Variable As FieldName In DLookup

Aug 12, 2014

I want to use a variable as FieldName in the DLookup function.

Normal DLookup:

Code:
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

I want to use:

Code:
DLookup(variable, "TableName", "Criteria= 'string'")

Where variable is an integer, which represents the column number in the table.

Is this possible?

View 7 Replies View Related

General :: Loop Function With Variable

Jun 4, 2015

I want to make a loop like this:

Dim var1 As Variant, var2 As Variant, var3 As Variant, var4 As Variant
DIm var5 As Variant, var6 As Variant, var7 As Variant, var8 As Variant
Dim var9 As Variant, var10 As Variant
Dim i1 As Long
i1 = 1
Do Until i1 > 10
var & i1 ??? = "0" & i1 & "." & txt1 & "." & txt2
i1 = i1 + 1
Loop

How to make concatenate var + i1 to make loop function?

View 10 Replies View Related

General :: Using Variable Between HTML Tags?

Sep 30, 2012

TextBox1 is Rich Text:-

I want to replace my String ("Evidence required of this please" ) in this code 1) following which works fine, with a variable (textToPaste) as in code 2) which doesn't work.

1) TextBox1 = "<div><font style=""BACKGROUND-COLOR:#FFFF00"">Evidence required of this please</font>"

2) TextBox1 = "<div><font style=""BACKGROUND-COLOR:#FFFF00"">textToPaste</font>"

Can I persuade the statement to accept "textToPaste" as a variable and not a normal string?

View 3 Replies View Related

General :: For Control Variable Already In Use Error

Apr 15, 2014

Code:
Private Sub CommandButton3_Click()
Dim rngCell As Range
Dim i As Long
Application.ScreenUpdating = False
'Coverage code
i = Range("J" & Rows.Count).End(xlUp).Row

[Code] ....

Why doesn't access like the underlined (For control variable already in use).

View 3 Replies View Related

General :: Variable Button Label

Oct 28, 2014

I have a printed form that pulls some of its data from a table.What i need is that printed form to have a variable that two area in the form can change based on who is printing the form.I have a table that has all the users data in it. the current form pulls that information from the label and places it into the for. easy enough so far right.

for example. But i need this printed form to be able to be dynamic. So for instance Tom inst sitting that the computer wanting to print the form and so on for four different users who will print the form.So the button that allows the print either needs a way to have a selection or i need four different button. So the second part of this Mark Twain quits his job and Johnny takes his place. easy enough to change the data in the table that my form is pulling the data from but the button label needs to change to that the button.

View 7 Replies View Related

General :: Select Top 100 In Query Variable Text Box

Nov 6, 2012

i have got a query that returns the top 100 results. i would like to link this to my report form where i have a text box that you can enter a number and the query returns the first of that ammount rather than going into the query everytime to adjust the results.the sql of the query is as follows

PHP Code:

SELECT TOP 100 tblClientDetails.FirstName, tblClientDetails.Surname, Sum(tblOrdersItems.Cost) 
AS SumOfCostFROM (tblClientDetails INNER JOIN tblOrders ON tblClientDetails.ClientDetailsID = tblOrders.ClientDetailsID)
INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderIDWHERE (((tblOrders.OrderDate)>DateAdd('yyyy',-1,Date())))
GROUP BY tblClientDetails.FirstName, tblClientDetails.SurnameORDER BY Sum(tblOrdersItems.Cost) DESC; 

View 5 Replies View Related

General :: Content Of Variable Crashes VBA Code

Jun 16, 2015

I have a form I use to create photo captions. The form has a field where users can edit a pre-generated caption and a field that stores their updates as a final caption in the field "selected_caption".. On my form is a button that runs the code below to check and see if a field ("updated") has been marked true, then copies the content of caption field into the selected_caption field. It works fine 90 percent of the time but for some reason this caption:

LA 109535, LA109535 partial site overview showing blowout with PL's 01- 04. Crew person is excavating a shovel test probe. Photo direction facing southwest. (Roll 31066, Frame 4958)

Results in a missing operator error. I thought it was like a special character thing, so I removed the "'" and the "-". Same error.

Code:
Dim db As Database
Dim rst As Recordset
Dim upcap As String
Dim strSQL As String
Set db = CurrentDb

[Code] .....

View 4 Replies View Related

General :: Enter Single Variable As Criteria For A Query?

Oct 10, 2014

I have a database structure with a series of queries which are all inter-linked.

I want my database to be user friendly, what is the cleanest way to be able to enter a single variable eg as a criteria for a query - say the date, rather than having to change it in a series of queries manually, can I call it from somewhere?

View 8 Replies View Related







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