Dealing With #error In Query
Apr 10, 2008
I am using the following to extract date information from a text field as part of a query.
Narrr_Date:CDate(Left(Mid([si_narr_t],Instr([si_narr_t]," ")+1), Instr(Mid([si_narr_t],Instr([si_narr_t]," ")+1)," ")-1))
Due to users not putting the correct information in I will get the #Error in the results of the query.
I need to do a DateDiff on the results so I need to get rid of any values that come up with #Error.
Since I cannot ensure the date extracted by the query is correct, how do I deal with the error?
View Replies
ADVERTISEMENT
Feb 4, 2007
What would be the best way to store data in a database that could change but without effecting previous linked records?
Let me try & explain a little better.
I have a table that lists materials with the cost price & sell price of each item
When I use that info to create a record I will only store the id no that the material is related to then link the cost fields to display the prices without storing the data again.
The problem I face is that in the future if any of the costs are changed in the main table then all linked records will also show the new amounts.
what's the best way around this?
Do I simply store the actual costs in the records when they are created allowing only the current costs to be used ?
View 2 Replies
View Related
Apr 13, 2007
I want the program to input an entry for every item with a value ranging from 1-300, according to sales data, according to the associated style.
I have to sort large amounts of Data and format them into a way I can deal with it.
I get two inventory lists twice a week from 16 stores.
I sort this data in two ways.
I do this by comparing an inventory list to a list of 300 of the best selling styles.
Currently, I input one list of inventory data in excel and then organize the items manually and respectively to sales data list in columns. Top 25 styles, 50, 100, 200, 300.
When I am finished, I have a certain amount of items in the top 25 styles, a certain amount in top 50, and so on. I color code the columns and write down the quantities and percentage of each category in the list..
if this is still confusing:
I have two tables.
one has the style numbers of which several items are in the category of a style number.
and one is the list I want to sort the previous list by. It has 300 styles that sold the best. they are rated from 1 to 300.
I want to organize all my items in each list by the 300 style list.
I have to do this twice a week, for 16 stores, for two separate reports.
ongoing basis.
is there a way of having the program do the numbers for me?
I am entering the data in excel physically . Is it possible to do this another way?
I am trying to sort the sales data sheet INTO the columns I have set up AND according to the styles that each piece of data has in common.
View 2 Replies
View Related
Dec 2, 2007
Hi everyone,
What is the best solution to deal with different currencies in an Access Application? After creating a table that includes currencies codes I want to use them in the form to present and calculate prices.
After choosing the currency from a combobox, the price field must be presented in numeric values with the chosen currency code.
Thanks in advance,
Best Regards
View 14 Replies
View Related
Dec 14, 2007
I have a database that has the basic tables of products, accounts and orders.
I need to figure out how to deal with returns.
Do I create a separate table and link that to the orders table for returns?
I haven't made such a large database file so I was curious if anyone had any information on how to lead me in the right direction. Or if there were any examples. (I've looked around but haven't completely figured out the best way to handle this.)
Thanks!
View 4 Replies
View Related
Oct 12, 2005
I have a database that keeps track of training hours for each employee. The Training Length is formated as Short Time. I just figured out that short time can only go up to 23:59:59. Some of my trainings will be over that. Does anyone know a way to get around other than splitting up my hours and Mins in the table?
View 1 Replies
View Related
Mar 14, 2006
This is for anyone who has made a form with a lot of check boxes and wants to make a report out of them thats decent.Hopefully this simple example file is enough to assist people.Keywords:Checkbox Checkboxes report check boxes box
View 1 Replies
View Related
Jan 5, 2005
Setup
i have a form (frmCustomers) with a subform (sfrmJobs). As you might have guessed, it keeps track of customers and jobs. One customer may have many jobs. within sfrmJobs are about 300 or so controls for about 300 or so fields (mostly yes/no boxes) and at the moment contains two subforms of its own. customers have an autonumber CustomerID and jobs have a user-entered numeric JobID.
Goal
it has been brought to my attention that some jobs may just not work out and must be deemed as "cancelled." however, my client wishes to retain these records rather than delete them. in order to maintain the integrity of the reports, i would like to remove these records from the non-cancelled customer database and place them elsewhere, but still retain the ability to review them if need be (but NOT to modify them).
Problem
the job can be cancelled at any point down the line, meaning that a job could have nearly no info or nearly all the info, and everything in between. each job record has a ton of fields. i don't know how to cut and paste an entire record (except for doing it field by field, which could take forever in this instance) into a new table. in this case, it would be several records, since i have a list of materials pertinent to each job as well as a list of services necessary for each job - all of which would have to be removed and placed elsewhere.
Cry for Help
what is the best way to go about this? some ideas i had were creating a parallel table for each table used in recording all of the job information and just manually (well, via VBA and recordsets) copying, pasting, then deleting. another is creating a checkbox that when clicked disables every control for the cancelled job (but oy vey that's a lot of controls to code the disabling for) and then have it reflected in my reports that these are only from the NON-cancelled jobs. does anyone else have an idea?
help?
thanks in advance
-Jason
View 2 Replies
View Related
Feb 11, 2005
Hello all,
Ok, let's see if i can explain this so that anyone trying help can understand. I have a form that's unbound (Music) with a subform (Arist Info)that is bound. The subform also contains two other Subforms (bound). The subform (Artist Info) contains a few Macros set up to do
some general searches with some Option groups and also one cmb box with vb code that access wizard wrote up. They all work great, with no problems at all when I open up the subform all by itself. It's when Main form is open where i run into some glitches. In then option groups all tgl & radio buttons work with exception to the two named ALL. I get a ballon message stating There is no field named
'GenreGroup or AlphaGroup' in the current record. Genre and AlphaGroup are the names of the option groups. The cmb box dosen't even work at all. There ars some other problems also but i'll ask for help later if i can reslove this. LOL I've attached a bare bone sample db to look at. I thought I was doing great learning new things every day, till I ran into this one.
Thanks for your help
Rich M.
View 4 Replies
View Related
Jun 12, 2014
I have a database that was populated using many excel sheets and one of the fields [partDescription] uses the # character over and over to represent "lbs". I realize that this is a wild card for digits in VB. Is there a quick fix for finding and replacing this character?
View 2 Replies
View Related
Aug 3, 2006
Hello...
I created a button that opens a link to a URL and copies information from a record to the clipboard to paste on this webpage.
The users are not using it correctly (they need to close the page each time they need to do the next record).
what i want to do is to close ALL IExplore windows via access if there are any open then open the URL again. Sounds pointless but we have to close it because of security.
Thanks.
View 1 Replies
View Related
Nov 2, 2007
I have an imported file coming from another department and the one of the field names I need to use has spaces. I have tried " " and [] but they don't work. What is the proper syntax for this?
origfield = rst.Fields("FVH PLAN")
Thanks.
View 1 Replies
View Related
Jun 25, 2013
I have a form that I designed that contains two subforms.The first subform is an entry subform so you can post the current client visits.The second subform I want to just display all prior client visits and make updates or deletions if needed.
It does not appear that I can make the first subform entry= yes additions allowed=YES in the properties.The second subform I want entry=No and addition=no..When I pull up the properties sheet for each of the subforms these options do not appear. They appear only on the main FORM, FORM property sheet. I thought I could create two new forms with the properties I desire and then copy them into the main form.
View 5 Replies
View Related
Feb 26, 2014
I have a database with student information that contains tables about their dissertation and graduation information. There is a field "academic year" noting their graduation year. I have a form for data entry that my data entry person likes to use in datasheet view. The form is based on a query that contains only current academic year records. When a new academic year arrives, I plan to create a new query for the form to feed from. i.e., "hiding" past academic year records on the form in datasheet view.
View 1 Replies
View Related
May 11, 2006
Hi all,
I am in desperate need of some advice. Today I set to work building some math functions in my queries to calculate delays/turnaround times for work, etc.
I have found that where there are null values in my table - the calculation seems to break down.
Let me explain...
In it's simplest form (I have removed some fields and will deal with DATE only, not TIME which is a seperate field) I have the following -
Date_Received
Date_Started
Date_Pended
Date_Recommenced
Date_Completed
I have the following expressions in my query -
Turnaround: Date_Completed - Date_Received
TimePended: Date_Recommenced - Date_Pended
TimeInactive: Date_Started - Date_Received
Workrate: Turnaround - (TimePended + TimeInactive)
The query calculation will only work when all fields contain a value.
For instance, sometimes the work may be started, then completed. Skipping the 'Pended' and 'Recommenced' steps.
Can anyone tell me what I need to include in my maths for Access to treat a null value in the table as zero? Or any other approach.
Please help!!!!!
Thanks
Rob :eek:
View 3 Replies
View Related
Jan 20, 2012
I have a large amount of field data that was taken by a data logger. Specifically, The datalogger has five temperature probes embedded in soil, and takes a measurement automatically once every hour for each probe. So the table has a Time field, and then a Temp1, Temp2,... Temp5 field. I need to be able to average the temperature fields. This within itself is easy enough, I just tell the query to find (Temp1+Temp2+...+Temp5)/5. However, unfortunately, because it is field data is is very common for one (or more) of the ports to be malfunctioning at any one measurement time for a number of reasons.
If, for instance, Port one does not have a value, then the formula I entered will not work, as it can't add a null value to numbers, and otherwise It would be dividing by five whereas there were only four values to average.
View 3 Replies
View Related
Apr 4, 2014
I am trying to use the expression below to transpose strings of 4-6 numbers (140504) to a string of 8 numbers with "20" in front (20140504). Some of the [Receive_Date] fields are empty or are 0, and in those cases, I need the expression to return a null value.
New_Receive_Date: IIf([Receive_Date]=0,"",IIf([Receive_Date]="","",Format([Receive_Date_YMD],"000000")+20000000))
The Format(...) part of the expression works fine on its own, but I am new to dealing with null values in my expressions, and when I add the rest of the expression, all '#Error' values are returned.
View 2 Replies
View Related
Aug 12, 2014
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
View 9 Replies
View Related
Sep 28, 2005
I am really stuck. I have spent two days searcinh different forums trying to solve my problem. I am trying to create an UPDATE q to my Access database. But I get either the: "Syntax error in query. Incomplete query clause" or "Syntax error in UPDATE query".
First of all here's the URL: www.innotec-as.no/login/Kunder
Login U/P either: "alfen" or "thomas".
The page opening up shows the user info, U/P and adress.
viewing the information is working perfectly - but editing it..no way.
When editing and submiting the data the above errors occour.
Try that and you'll also see the SQL I am trying to execute.
The CODE is as follows:
SQLtemp = "UPDATE 'Brukere' SET"
SQLtemp = SQLtemp & " 'navn' = '" & request("Navn") & "', "
SQLtemp = SQLtemp & " 'epst' = '" & request("Epst") & "', "
SQLtemp = SQLtemp & " 'Pass' = '" & request("Pass") & "', "
SQLtemp = SQLtemp & " 'Firma' = '" & request("Firma") & "', "
SQLtemp = SQLtemp & " 'BAdresse' = '" & request("BAdresse") & "', "
SQLtemp = SQLtemp & " 'BPostAdr' = '" & request("BPostAdr") & "', "
SQLtemp = SQLtemp & " 'PAdresse' = '" & request("PAdresse") & "', "
SQLtemp = SQLtemp & " 'PPostAdr' = '" & request("PPostAdr") & "', "
SQLtemp = SQLtemp & "WHERE 'Bnavn' = '" & request("Bnavn") & "'"
Response.Write(SQLtemp)
Response.End()
conn.Execute(SQLtemp)
rs.Update[/COLOR]
The finished SQL statement looks like this:
UPDATE 'Brukere' SET 'navn' = 'Alf Byman', 'epst' = 'alf@baccara.no', 'Pass' = 'alfen', 'Firma' = '', 'BAdresse' = '', 'BPostAdr' = '', 'PAdresse' = 'sdfg', 'PPostAdr' = '', WHERE 'Bnavn' = 'alfen'
I have tried to user single quotes, doubble quotes, brackets etc. nothing works.
The code I use for connection is as follows:
<!--#include file="../adovbs.inc"-->
<%
dim conn, rs, SQLtemp
' DSNless connection to Access Database
set conn = server.CreateObject ("ADODB.Connection")
rs="DRIVER={Microsoft Access Driver (*.mdb)}; "
rs=rs & "PWD=uralfjellet; DBQ=" & server.mappath("../../../../db/kunder.mdb")
conn.Open rs
I'll be very HAPPY for some expert help on this.
View 1 Replies
View Related
Apr 22, 2015
All I'm trying to do is a make table (table name to overwrite: "TBL_5_7_RFCs") query via VBA so I can loop through a set of VARs in a table. Here is my code:
Code:
Public Function test_sql()
Dim Conn As ADODB.Connection
Dim RS As New ADODB.Recordset
Dim MySQL As String
Set Conn = CurrentProject.Connection
RS.ActiveConnection = Conn
[code]...
I'll add the loop and array once I get this working but I'm stuck.
View 7 Replies
View Related
Nov 20, 2007
Why can't I sort ascend on this in a query:
a_aircraft_number: (Val([aircraft_number]))
???
View 4 Replies
View Related
Jul 22, 2005
hi............
querycompany ="SELECT distinct company_id,company_name,location_id FROM appointment_detail,company where appointment_detail.location_id="&request.querystring("location")&"ORDER BY company_name"
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'appointment_detail.location_id='.
/sd/jobsearchasp60.asp, line 33
pls sort out
View 3 Replies
View Related
Jun 15, 2006
here is my query
Code:SELECT FormatPercent(COUNT(ThreeWeekStatus.Status)/subQuery.Total) AS OnTimeTasksFROM RawData, ThreeWeekStatus, [SELECT COUNT(ThreeWeekStatus.Status) AS Total FROM RawData, ThreeWeekStatus WHERE RawData.OutlineNumber=ThreeWeekStatus.TaskId]. AS subQueryWHERE RawData.OutlineNumber=ThreeWeekStatus.TaskId And ThreeWeekStatus.Status="On Time";
and get the error "you tried to execute a query that does not include the specified expression....
basically have two tables
ThreeWeekStatus (has TaskId and Status as columns)
RawData (OutlineNumber, OutlineDescription, Category, etc)
RawData. Category has values like planed tasks, milestones, unplanned tasks, etc.
ThreeWeekStatus.Status has values like on time, delayed, completed, late
TaskId and OutlineNumber are the same
now I want to take all the tasks that are in both ThreeWeekStatus and RawData and get a percent of of which tasks are completed. also want to group this by category so in the end have it as 80% of the tasks marked as milestones have been completed thinking of the following for the percentage...
(count number of tasks in both tables and marked as completed in ThreeWeekStatus table)/(count total number of tasks in both tables)
want to have similar queries for tasks that are on time, delayed, and late so there should be four
View 4 Replies
View Related
Jun 15, 2006
What I am trying to do is select the Max value in a field in a table in Access and set it to a variable. If I run the query from below in the query window it runs....when I try and launch if from code it bombs out....gives me an error:
ERROR:
Run Time error '2342'
A RunSQL action requires an argument consisting of an SQL statement.
I have other SQL queries in my code and they are running fine...whats differene about this one. How can I run it from the Query window and not from code?
CODE:
strSQL5 = "SELECT Max(POINTS_TO_PLOT_SQL.ID) AS MaxOfID FROM POINTS_TO_PLOT_SQL;"
DoCmd.RunSQL strSQL5
View 5 Replies
View Related
Oct 10, 2007
I have "New Campaign" set as a Yes/No data type, and Income as currency. I have five different levels I'm trying to divide SumofGrossIncome into, such as
$0 to $750,000
$750,000 to $1,500,000
and so on...
These levels only work on existing campaigns. If it's a new campaign, I have different levels to go by.
In my query, I typed:
Level A: IIf(([New Campaign]="No") And ([SumOfGrossIncome] Between 0 And 750000),"A")
I'm getting the error "This expression is typed incorrectly, or it is too complex to be evaluated."
Am I missing something small, or totally off base?
Thanks.
View 6 Replies
View Related
Sep 15, 2005
With ColdFusion and MSAccess 2003, the following query :
SELECT A.*, B.entry_id
FROM A.blog_entries, B.blog_subscriptions
WHERE B.blog_id = #variables.blog_id#
AND B.user_id = '#variables.userInfo.user_id#'
AND A.user_id = B.user_id
returns this error :
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:WINDOWSsystem32B.mdb'.
Please advise. ThankYou
View 4 Replies
View Related
