Query Results From Iif Statment Confusing
Jun 1, 2006
I've got an unbound form which has a field that I need to relate to from a query, I've got no problems relating to specific records, but I've not quite cracked the ability to select all other records if the field is left at 0.
I've tried using in the query under the 'table' field (for selecting a specific group of people seated at a particular table)
Iif([Forms]![TablePlannerFrm]![TableSel]=0,>0,[Forms]![TablePlannerFrm]![TableSel])
Which I thought would evaluate a true/false with the two results coming from the two options, so if the Table selected on the unbound form was set to 0 then it would set the criteria to >0 (everything table 1 and up), or if anything else was selected other than 0, it would use the number from the unbound form from the TableSel field.
I'm getting blank results from both positive and negative results. I assume that I'm not using the Iif statement correctly, or that there is another way of approaching the problem that my headache is blocking :)
Thanks everyone
View Replies
ADVERTISEMENT
Aug 11, 2005
This is a bit confusing, so I'll try to explain it well.
Two tables. One has a list of metals. The other has a list of submetals, and what basic type of metal it is. It's grabbing the second column from the first table. Makes sense, right?
Now, I have another table, with links to both of those fields. What I want it to do is when it picks the metal type, to filter the subgroup to only the types it has associated with it to the basic metal type. Not sure if that makes sense. I'll supply an example.
Table 1
Aluminum
Brass
Copper
Table 2
Subgroup Basic group
License Plate Aluminum
Iron Aluminum Aluminum
Yellow Brass Brass
Copper Wire Copper
Now, if in Table 3, they choose Aluminum from the drop down box for basic metal, I want the dropdown box for the subgroup to have only License plate, and Iron Aluminum. For Brass, only Yellow Brass, and the same for copper.
Any ideas on how to implement this?
View 3 Replies
View Related
Nov 21, 2006
Anyone knows if I can predefine decimal places in a query statement? For example, when I run the select query below, I want the MedicalPremAmt to have two digits after the "." It should look something like this: 12.38
Thank you.
SQL = "Select [Barg Unit], First([Medical Option]) As Carrier, First([Medical Coverage Tier]) As Tier, Sum([Medical Premium Amount]) As MedicalPremAmt,Sum([Total Grant]) As TotalGrant,Sum([Health Allocation]) As HealthAllocation,sum([Medicare Allocation]) As MedicareAllocation FROM RetireeCensus Group By [" & Category & "];"
View 1 Replies
View Related
May 12, 2005
Ok this may seem very confusing as alot of things need to happen for what I am trying to do
I am doing an inventory database that contains all order, client, purchasing, and inventory information.
I have a table that has all of the different products we sell (also includes which of the following tables are needed) I also have separate usage tables storing how many of each part (given a partnumber) for each of the products.
Now this is where the complecation comes in .When I create an order I want to 1. add a record to my order table (containing ordernumber, client number, product purchased) 2. using the product table, find out which usuage tables need to be looked at, go into those usage tables, get the number of each part that is needed for the product and store the value into an allocation table
I have had this on my mind for the past 2 weeks that I have know I will have to do this at some point. I am getting to the point where I cannot do anything else without this being done. I need help something bad.
If you feel you know how to do this and think you need more information, email me at cgow1@cogeco.ca
View 1 Replies
View Related
May 1, 2007
Hello, Im creating a database for a charity just to get some experience and im stuck on something - my table structure.
I know you might find it a bit difficult because you dont know the background info but i need the database to be able to let customers and members make bookings. Ive thought of putting both customers and members in one table but i dont want it to get complicated and messy, although i will do that if there is no other way.
The ER-D is attached.
17309
Is there a solution to my dilemma?
Ant...
View 2 Replies
View Related
Feb 7, 2005
Hi everyone,
basically what i need to able to do is set a validation rule that allows me to enter a value that is between 5 and 10 kgs but (and here comes the confusin bit is also rounded to the nearest 5kg. I tired using the[ like "?.?0" or "?.?5" ] command but have so far been unsucessful. Also i really like oranges.
View 5 Replies
View Related
Nov 8, 2005
One of our key customers (GE) has demanded that we begin to submit our estimates in their transmission 160 character format.
Basically it it a txt file that looks like this all on one line.
1ABCDWXYZ0502EST TEST123456T050208654321C 00010851803098518031 000000E 0000000000000D CAR SHOPPED AS CLEAN-PROCEED WITH
Only a couple of those characters are in my database and will change. Other than that items will continously be the same. The items that are optional and we don't have not only have to be blank but have to have spaces there instead...not 0's.
How do I set up a query to kick all of this out? I tried something like:
GEString:2UPRR YYMM00&""&WorkOrderID yada, yada, yada
but it didn't work....any ideas? I also need the YYMM to be the current year/month and I have to pull the decimals out of the estimated billing costs.
Is there a better way to do this? As you can imagine...typing 5+ pages of this for each job would be a NIGHTMARE!
View 13 Replies
View Related
Oct 6, 2007
I am trying to redesign an older DB for a school,
to meet the new needs that emerged.
I am totally confused due to the complexity of the problem. I've already searched in this forum and in Google but most articles are for simpler DBs.
Well to get to the point.
School has Students,
Students attend to Classes (many to many)
Students are assigned Lessons (many to many)
and coming to the confusing part
a Lesson in specific Class may be teached from two different Professors
(e.g. the 1st may teach the theory and the other the excersizes).
So another many to many Class-Professor and another Class-Professor-Lesson and so on?
After throwing away many sheets of paper I come up with a schema which seems a little bit strange but seems to going to work for my case.
I figure out that I could have a unique junction table storing all these info, and that's no other than the Schedule table.
So I'm thinking of having the following relationship schema:
Students
StundeID
Name
etc.
Lessons
LessonID
Title
Classes
ClassID
Title
Professors
ProfessorID
Name
etc.
Schedule
ScheduleID (maybe a combination of all others)
ProfessorID,join with Ptofessors
ClassID, join with Classes
LessonID, join with Lessons
Day
Time
Schedule_Students
tableID
ScheduleID,join with Schedule
StudentID, join with Students
What is your oppinion about? Can you see any problems which may emerge from such a design?
Thanks in advance
View 8 Replies
View Related
Sep 7, 2006
I have typed this SQL in a query; however, access doesn't seem to like it. Does anyone have any idea why please?....
View 13 Replies
View Related
Dec 28, 2006
I am having issues with a multiple option on my If Statement. I do not want to create and If statement for each if I can combine what I want it to do.
If [cboType] = "Class II with 16K / 16K" Or "Class III with 16K / 16K" Or "Class III with 32K / 16K" Or "Class III with 32K / 32K" Or "Class IV" Then
The code just die when it is combined. Is OR and option.
Thanks
View 3 Replies
View Related
Oct 15, 2004
Dim mysql
mysql = "insert into [OwnerOccupierDetails] (owner_no,owner_name,owner_address,owner_phone,own er_confirmation,owner_reps,owner_reps_planner,owne r_herd_no,owner_area_status,owner_date,owner_detai ls,occupier_no,occupier_name,occupier_address,occu pier_phone,occupier_confirmation,occupier_reps,occ upier_reps_planner,occupier_herd_no,occupier_area_ status,occupier_date,occupier_details,source_name, details) VALUES ('" & Me.[txtownerNo] & "','" & Me.[txtownerName] & "','" & Me.[txtownerAddress] & "','" & Me.[txtOwnerConfirmation] & "','" & Me.[txtOwnerReps] & "','" & Me.[txtownerRepsplanner] & "','" & Me.[txtOwnerHerdNo] & "','" & Me.[txtOwnerAreaStatus] & "','" & Me.[txtOwnerDate] & "','" & Me.[txtOwnerDetails] & "','" & Me.[txtoccupierNo] & "','" & Me.[txtoccupierName] & "','" & Me.[txtoccupierAddress] & "','" & Me.[txtOccupierPhone] & "','" & Me.[txtOccupierConfirmation] & "','" & Me.[txtOccupierReps] & "','" & Me.[txtOccupierRepsPlanner] & "','" & Me.[txtOccupierHerdNo] & "','"
& Me.[txtOccupierAreaStatus] & "','" & Me.[txtOccupierDate] & "','" & Me.[txtOccupierDetails] & "','" & Me.[txtSourceName] & "','" & Me.[txtDetails] & "');"
DoCmd.RunSQL mysql
im writing this really long query in vb so it needs to go on two lines, but whats the syntax for going on the next line(theres a quote or something but i cant remember for carrying on from one line to the next)
View 6 Replies
View Related
May 2, 2007
Hello again all... Today's problem is as follows.... I have an "after update" combo box that is coded as "CurrentDb.Execute "uno", dbFailOnError". I have an SQL Query named "uno" that is showing the following: "UPDATE [Input] SET [Input].Loc_Lng = [Loc-1L].Combo8, [Input].Loc_ID = "1", [Input].Loc_Desc = "xxxx";" I am trying to have the combo box update the values from the combo box into a blank table. When I try to run this, it comes up with the error: "Too few parameters. Expected 1". Any idea what that means, and then how to fix?
View 2 Replies
View Related
Aug 15, 2006
Hi there!
I've got the following code on my database:
strcriteria = "[date] = #txtDay1.value# "
Set rst = dbs.OpenRecordset("tblPlanner", dbOpenDynaset)
rst.FindFirst strcriteria
The problem is when I run the code it have the following error:
Run time error '3077'
"syntax error in date in expression"
I've formated boths date field and txtday1 on short date.
Is there anyone that could tell me what wrong am I doing?
Thanks
Jonybravo
View 3 Replies
View Related
Sep 29, 2004
I am very new to ASP
I would like to learn how we can insert a data from a web form into MS access file.
This is the situation
Well i have a db.mdb file located at c:/www/db.mdb
db.mdb contain only one table that is named as students which in turn contains only three columne i.s StudentsID , FirstName and LastName
StudentsID is autonumber field in db file and FirstName & LastName are text fields
Now i have a self.asf file where i have a form with only two input fields
i.e
<form action=self.asp method=post>
<input type=text name="first" value="">
<input type=text name="last" value="">
<input type=submit value=submit name=submit>
</form>
Please let me know how i can write a SQL insert statement and database connection so that i can insert the form data into the db.mdb file
With Regards
View 2 Replies
View Related
Jan 12, 2005
I have a select statement that returns information into a sting
Then I try to insert this value into a table but I am not sure of the correct syntax. The SQL select statement works but not the insert into...
sRequirement1 = category1.value & ">" & subcategory1.value & ">" & Me!Label1.Caption
selectRequirementid1 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement1] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "
DoCmd.RunSQL "INSERT INTO Hours ([RequirementID1]) VALUES( " & selectRequirementid1 & ");"
not sure how to treat selectRequirementid1 in Insert into statement
I tried the following yet non work...
'" & selectRequirementid1 & "'
'" + [selectRequirementid1] + "'
View 12 Replies
View Related
Nov 8, 2006
I was wondering if anyone knows how to insert more than one record at a time with a SQL statment? I have a form that asks the person how many records they would like to put into the system, this is a text box that they can enter a number.
My SQL for input one record is this:
[CODE]
SQL = "INSERT INTO tblTemp ([Name],[Number]) VALUES (TName,Len)"
DoCmd.RunSQL SQL
If the person wants to put in more than one I want the system to take the Number field and add 1 to it for ever record the person asks to be put into the system. the Name and Number fields will be that same except the Number field will increase by 1 for however many they choose to put in.
View 11 Replies
View Related
Nov 20, 2007
Hello guys and Girls
I am trying to setup a query that will return only the records in a date range that is user specified. In the Criteria of the date field I entered this
Between [Forms]![Report Switchboard]![txtStartTrend] And [Forms]![Report Switchboard]![txtEndTrend]
But the query does not work. Is the syntax correct? Both txtStartTrend and txtEndTrend are format Short Date.
Thanks for any Help
Anthony
View 1 Replies
View Related
Jun 13, 2005
hello all,
i have a pretty simple problem related to ACCESS query.
I have two tables having similiar columns ( i had to do this to compare the values given to me, because both the table data came from different sources)
The tables named are
OFFICERS
token
full_name
division
EMPLOYEE
token
full_name
division
Now the table EMPLOYEE contains more than 5000 records and the table OFFICERS contains around 2400 records which are already present in the EMPLOYEE table.
Now I want to build a query that will return me all the records in the EMPLOYEE table that are not present in the OFFICERS table. The criteria for comparing 'full_name'. so the query should return me 2600 records that are not present in the OFFICERS TABLE.
I tried doing this
Code:" SELECT DISTINCT EMPLOYEE.full_name,EMPLOYEE.token FROM EMPLOYEE,OFFICERS WHERE EMPLOYEE.full_name<> OFFICERS.full_name"
But this query returns me records that is not present in the OFFICERS table as well as some more 1000 records which are present in both tables.
please do help
thanks a lot
View 1 Replies
View Related
Jun 22, 2005
problem with this insert statment in vba
Hi guys i got vba code that suppose to write table name ,column name , rquired ,feild type and feild size
to an external db. The first part of this code write tables naem to external db and it is working well.
I get the following error when i press the button on my form :
Code:Run-time error '424':Object required
and when i click on debut it points to this part with yellow collor.
Code: metadb.Execute " Insert Into SysColumns(tablename,columnname,required,type,leng ht) " & _ " Values ('" & TableDef.Name & "','" & Feild.Name & "'," & Feild.Required & ",'" & FieldType(Feild.Type) & "'," & Feild.Size & ")"
I be happy if some one help me fix this erro.Thanks
my complete code
Code:Sub InsertSystemCatalogPopulation(db As Database, metadb As Database)'''now locating all the non system tables in current db and then writing it'''to systables tableFor Each tbl In db.TableDefs''' excluding the system tables If Left(tbl.Name, 4) <> "MSys" Then ''' writing the tables name to systables metadb.Execute " Insert Into SysTables(TableName) Values ('" & tbl.Name & "')" End If Next tbl MsgBox (" All tables names coped to systables system cataloge ") '''############################################### #################################### ''' now we go find all non syste feilds in the current db and then writing ity ''' to syscolumns For Each TableDef In CurrentDb.TableDefs'''this if statment remove the system feilds If Left(TableDef.Name, 4) <> "Msys" Then For Each Field In TableDef.Fields metadb.Execute " Insert Into SysColumns(tablename,columnname,required,type,leng ht) " & _ " Values ('" & TableDef.Name & "','" & Feild.Name & "'," & Feild.Required & ",'" & FieldType(Feild.Type) & "'," & Feild.Size & ")" Next Field End IfNext TableDefEnd SubFunction FieldType(intType As Integer) As String Select Case intType Case dbBoolean FieldType = "dbBoolean" Case dbByte FieldType = "dbByte" Case dbInteger FieldType = "dbInteger" Case dbLong FieldType = "dbLong" Case dbCurrency FieldType = "dbCurrency" Case dbSingle FieldType = "dbSingle" Case dbDouble FieldType = "dbDouble" Case dbDate FieldType = "dbDate" Case dbText FieldType = "dbText" Case dbLongBinary FieldType = "dbLongBinary" Case dbMemo FieldType = "dbMemo" Case dbGUID FieldType = "dbGUID" End SelectEnd Function
View 1 Replies
View Related
Apr 29, 2008
Here is my CASE WHEN statement in SQL. What would the If statement be in MS access?
CASE WHEN LEN(Serial) = 9 THEN Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 8 THEN '0'+Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 7 THEN '00'+Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 6 THEN '000'+Serial
ELSE 'Error'
END
AS NewSerial
Thanks for your help!
View 5 Replies
View Related
Dec 9, 2006
Hey All,
Im using MS Visual Web Developer with an Access Database, i am trying to enter a new row of data into a database via a select statment attached to a button_click event. the code i am using looks something like this.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.AccessDataSource2.SelectCommand = "INSERT INTO [Tblissues] ([IssueDate], [IssueTime], [PlantLocation], [MachineName]) VALUES ('12/9/06', '3:00 PM', 'Gville', 'Snack2')"
End Sub
that being said. when i click the button, NOTHING happens, no error and no insert!!!
I did test the connection to the Database and its fine, i can do an update and i can read the data that exists in the database i just cant insert a new row.
This is not the first time i have delt with INSERT statments but this has me at my whits end and any help would be great.
Thanks
View 1 Replies
View Related
Feb 1, 2005
"ALTER TABLE MOVIE ADD ( MULTIPLEX NUMBER(3))"
while i'm trying to execute this query ...
Its displayed as 'SYNTAX ERROR IN FIELD DEFINITION'
Pls help me.... :confused:
View 1 Replies
View Related
Apr 1, 2008
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
View 7 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
Jan 3, 2008
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD
etc...
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
View 7 Replies
View Related
