Queries :: Produce HEX And ASCII In Query Results
Sep 17, 2013
We have a need to produce hex and ascii in the same results field.
The field with data will be bound to a 2D barcode (the square ones you see everywhere now). The format of the data is to look like this:
[)><RS>06<GS>xyz<RS><EOT>
The <RS>, <GS>, and <EOT> all need to be hex values as follows:
<RS> = Hex 1E, Decimal 30
<GS> = Hex 1D, Decimal 29
<EOT> = Hex 04, Decimal 04
When I run my Access query as follows:
BarCode: "[)>"&Hex(30)&"06"&Hex(29)&"xyz"&Hex(30)&""&Hex(04)& ""
It produces the following:
[)>1E061Dxyz1E04
So, it just converts it to ASCII; however, when I go to scan it, it actually reads that information also. I need the 1E, 1D, and 04 to actually be scanned as HEX for the validation to occur for our client.
Is it possible using an Access query to return these desired results? If not, I will have to look atother piece of software to produce the bar code labels.
View Replies
ADVERTISEMENT
Jun 17, 2014
My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).
I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.
So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).
Which all works fine
However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.
But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.
I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.
View 1 Replies
View Related
Jun 27, 2014
I import data through different files into a table, the data comes in different formats. I have a problem sometimes some records have double quotation marks and carriage returns. I know how to replace them as
Carriage Returns
Replace([FieldName],Chr(13) & Chr(10),""
Double Quotation Marks
Replace([FieldName],Chr(34),""
But if i want to use an iif statement because in the field there could be a Double Quotation Mark or Carriage return then it does not find the record
iif([FieldName] like Chr(34),Replace([FieldName],Chr(34),"",[FieldName])
View 2 Replies
View Related
May 14, 2013
I have a table that is linked into access 2003. This table is updated by personnel in another location and I have to run a weekly report on engines that are below a certain performance level.
The column heading is MGT Margin and I have to list all of the engines that are below 20 degrees.
Can I run a query that looks at this table and produces a report of all the engines that are below 20 degrees?
I currently have to cut and paste each engine from the updated spread sheet every week onto a separate spread sheet and import that into access. If a query can be used to do what I am after I can use similar principles in other reports I have to run.
View 5 Replies
View Related
Nov 28, 2006
Guys,
I am trying to put together a query that will give me a calculation of days between the currect date and a date in the future.
How do I tell a query that I want to display the current date, and then calculate this?
View 1 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Feb 16, 2015
I have over 100 queries of weather data, for each month, and would like to display certain or filtered information for a selected month.
I have been playing around with a combo box to select a month from a given year, and was hoping that maybe an 'After Update' would do what I want. The problem I have is that I don't know how to do this, as the month picked and displayed in the combo box is variable , and so a sort of wildcard might do the job, if I knew how.
View 14 Replies
View Related
Mar 6, 2013
I have a form that produces a datasheet that derives its information from a query. The query works fine giving the desired information.
In this datasheet on "PurchaseOrderID" i have a On Dbl Click event that states this.
Private Sub PurchaseOrderID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FrmPO_Received"
stLinkCriteria = "[ItemID]=" & Me.ItemID
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
Now it works sometimes, but sometimes it pulls up a different "PurchaseOrderID". Is there a way to fix this. I thought maybe there would be a way to put two criteria, "ItemID" and "PurchaseOrderID".
View 2 Replies
View Related
Jan 28, 2013
I would like to build a query that has a calculated field, involving value from a previous record. I have five columns, Year (sorted ascending), InvestmentRate, StartingBalance, AnnualContribution, and InterestIncome.
The first row, being the smallest year value, uses a previously defined elsewhere starting balance, annual contribution, and interest income. This is recognized via an Iif statement and establishing the first set of values which the second row should use to calculate.
The second record, and until the end of the query, automatically calculates the new starting balance value based on the sum previous row record's starting balance, annual contribution, and interest income.
Furthermore, for the second record, and untill the end of the query, the InterestIncome is calculated value incorporating the second row's StartingBalance value (which is the product of the sum of the previous record's values).
This calculated referencing to the previous record is causing difficulties.
View 2 Replies
View Related
Jul 31, 2013
I am looking to call two different queries from report wizard to produce report. Getting error message what to do in this situation as both queries are important as i have to pick all records from query A and just one record from query B any other option to get this in report.
View 4 Replies
View Related
Mar 19, 2013
I have a query that is showing two results for one product and I have no clue why. I have my tables in a one to many relationship and if I click on the + it shows the correct data for the product in question. Im guessing I have my table set up wrong its the only thing I can think of.
The table tbl_Carton has two entrys for a product (Flex Tape 2-1/16") (Product_PKEY #21) mabe I have it set up wrong? If I run my (qry_Switchboard ) and enter (21) its giving me an extra result for each of the two results thats suposed to be there.why?
View 4 Replies
View Related
Jul 4, 2013
I'm having a mental block on this one.
I have a table with a yes/no field. It's linked to a form with a yes/no box, but the outcome I want is that if the form box is unchecked, the query returns all the "yes" records - which is trivial; however I want to return ALL the records if the form box is checked.
View 1 Replies
View Related
Jun 20, 2013
I have an access database which is going to present a front end webpage form. The form has several drop down lists and I need one of those drop down list to display only certain results dependent on what the previous drop down list has selected e.g. if Box A is selected as Aces then I need the next drop down box to only display A building
1-A building
2 A building
3 and not the entire list.
How can i get this to work?
View 2 Replies
View Related
Mar 13, 2015
In Access 2007, I put into the table, in a certain field, a certain word field, so I put into design view for that query, in that field, Like "Field" and even though the word field is in that table in that field, it doesn't show it in that particular query?
View 14 Replies
View Related
May 20, 2013
I need to do a calculation on the RESULTS of a query. Each record in the table I'm querying has a date stamp. I have a query that produces a list of the newest records for each machine. I want to calculate how old each of those records is based on today's date.
So far when I try to include the calculation in that query, it returns all the records instead of the newest ones. This makes me think I'm doing something fundamentally incorrect. The calculation is affecting the query results where I just want to do the calculation on the results. What I'm trying to ultimately do is have a way for the users to see a list of machines that are overdue for a certain procedure. I was thinking of putting a button on a form that will run the query and present the list, perhaps with some conditional formatting highlighting the machines that are overdue, etc. Do I need to just use 2 queries?
View 14 Replies
View Related
May 3, 2013
Have created a simple data collecting database with a simple query to narrow down some of the data , the DB all works fine and some queries are ok, but one the simple query where I want to look a one single set of data.Using the Like "*"&[Enter Search Parameter]&"*" gives me no results.
View 9 Replies
View Related
Nov 5, 2014
I have created a booking system for a travel agency and now when the booking is complete I want to export some summary data to the Excel. The issue is that I have separate tables showing lunch, tickets, tour guides, etc for every destination and I have also created queries showing the total for each destination but the issue is that I cannot combine all the results into one query and export that query to excel.
View 4 Replies
View Related
Feb 27, 2006
Hi All,
I have a approximately 70 queries in my database. I would like to be able to run a query which would run all of the queries and output the number of records for each query. Ideally, these would then be written to a table so that the user could then just read the values from the table for the latest results, rathe r than have to execute the whole thing again.
The user may wish to select which queries to run. I was thinking that I would need a table as follows called say tblQueryResults:
QueryToRun - Yes/No - DateRun - NumberOfRecords
Query1 - Yes - -
Query2 - Yes - -
...
...
...
Query70 - Yes - -
So my first dilema is to work out how to run all the chosen queries that the user wishes to run. The user will probably have all 70 ticked as Yes initially.
Should I run this from VB code with a whole lot of VB statements. I would like to loop through the whole table and collect a list of all the queries to run based on a positive Yes for some or all of the queries. The results must then go and be written into the same table under the date it was run and the number of records that was found for each query.
The whole reason for doing this is that queries which return no records need not be run by the user - saving the user time etc. I appreciate that this query will take a considerable amount of time - given that it could be as many as 70 being run one after another.
Thanks,
Evan
View 2 Replies
View Related
Aug 8, 2013
The results of my union query of 2 separate tables looks like:
Name - Count of Participations
Joe Blow - 7
Harry Smith - 11
Kate Upshaw - 8
Joe Blow - 3
Harry Smith - 5
Kate Upshaw - 13
I need to combine the duplicate names and total their participations to make a report look like:
Name - Count of Total Participations
Joe Blow - 10
Harry Smith - 16
Kate Upshaw - 21
View 3 Replies
View Related
May 20, 2013
i have created a parameter query which will find a students best and worst time for each exercise they have done. so you enter the student ID when you run the query and it works fine but i have a problem i need to query to find two students in particular and then i have to create a report from this query on the two students identified and this is where i come up stuck. i have tried typing in both student id's into the criteria but this doesnt work no matter if i put and in it or not and im not sure how to get the job done.
View 7 Replies
View Related
Nov 14, 2013
The Query
'SELECT Count(clubbox) AS MTSingles
FROM moves WHERE (((moves.cmrdate)>[Forms]![CPanel]![Text44] And (moves.cmrdate)<[Forms]![CPanel]![Text46]) AND ((moves.driverid)=[Forms]![CPanel]![Text38]) AND ((moves.move)=35) AND ((moves.clubbox)=32));'
returns about 60 records, how do i assign the results to a tempvars
View 9 Replies
View Related
Jun 16, 2015
From a Access database that I inherited. Users used to be able (from a form) run a query and then add data (i.e, new rows). But now that the the database is split and the backend is on MS-SQL, they no longer can do this. I do not think this will be possible. I think they will have to add data directly to the table or have another form for adding data.
View 3 Replies
View Related
Jan 29, 2015
Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).
A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).
I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...
My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case.
Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:
1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler)
2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)
View 7 Replies
View Related
Feb 17, 2014
I have a query that I am using in VBA to select the number of days equipment is past due for service. I have an equipment table with a last maintenance field (tbleqDP with field DPLstMnt) and another table (tblmaintdue) that defines the maintenance interval. I have a query (qrynumdays) that finds the number of days from tblmaintdue for the equipment type based on a value in cboMaintEquipType combobox. The VBA then generates a report based on which type of equipment is selected using a select case statement. An excerpt of the VBA is below:
Select Case cboMaintEquipType.Value
Case "DP"
Set qdf = db.CreateQueryDef("qryoverdue", _
"Select DPTag, DPLstMnt, DateDiff(""d"",DPLstMnt,Now())-NumDays AS Overdue from tbleqDP, qrynumdays where Overdue > 0")
DoCmd.OpenQuery "qryoverdue"
DoCmd.OpenReport "rptDPmntovrdue", acViewPreview
I am having problems with the "where overdue > 0" statement. It is asking me for parameter value for overdue. How can I get this to automatically filter out any values that are negative - in other words, don't report on equipment that is not past its maintenance interval due date.
Also, I think there is a way around how I am naming my query in the createquerydef function because later I just delete the query after the report is run. Is there a better way to do that.
View 2 Replies
View Related
May 10, 2013
I have a form that is set to display the results of a query into a subform... How can I get those results to a printer using a button. I don't need it to print the form just all the results that are in the subform.
View 3 Replies
View Related