Trouble With Code In Query

Jun 12, 2007

I am having trouble with some code I am using on a query. Part of the query comes from two fields in a form. The rest comes from a table. The query then populates a report. The code I have written works fine. The trouble starts when I try and enter in some code so that if some of the fields in the form is left blank, it just returns values based on the fileds that has data, so ignores any blank fields.
I have tried to use examples illustrated in this Forum, but it does not seem to like it. I keep on getting the request to fill in the parameter value. I am sure it is something simple!

This is the code I have entered for the Invoice Date Field:
Between [Forms]![Invoices]![Invoice date 1] And [Forms]![Invoices]![Invoice date 2]
(this is so the user can enter a date range with the two fields)

View Replies


ADVERTISEMENT

Trouble With Code When Record Is Locked

May 8, 2005

I'm going live with my DB tomorrow morning. I wanted to to Edited Records for my lock type but ran into a problem, this weekend when I took it to our office and had it on multiple computers for the first time. (I've been designing at my house, in my spare time (right), so I couldn't test for the problem I have now.) When a user is on a record and I open the same record up on another computer it locks ok and shows in the Record Selector that it's locked but if I begin to tab through the form I have at least one code that pukes. In my LastName field, in the On Exit event, I have the following code:
If Not (IsNull(Me.LastName)) Then
Me.LastName = StrConv(Me.LastName, vbProperCase)
End If
As I try to Exit this field, only when the record is locked, the code pukes. Can some one lend a hand and give some advise on how to prevent this?

Thanks ahead of time,
Shane

View 2 Replies View Related

Having Trouble In Executing TreeCtrl Code

Jul 11, 2006

Hi,

I am having trouble in implementing the TreeCtrl structure. I have a simple database for testing purpose and I am not able to make it work. There appears to be a problem with the bookmark property and I get an error message:
"can't Add child:Arguments are of wrong Type, are out of acceptable range, or are in conflict with one another".

I have attached the database here and could you please see what is wrong with my code.


Thanks
Priya

View 5 Replies View Related

Query Trouble

May 15, 2006

How do you use the "sqrt"-function in a query? This function is unavailable in a query - and i do not know why. Please help!?
Thanks

View 2 Replies View Related

Query Trouble

Feb 15, 2008

I have attached a small db that has 1 query with several fields..

Here is what I'm looking to do.

In the Query there is the following fields:

SignINMemberID
GuestOfMember
SignInDateTime
AllowedEntry

Now all these are derived from tdatMemberSignIn.

What I want to do is show the name that corresponds with the MemberID in table MemberList.

That means if the SignInMemberID exists it would show First and Last Name and if GuestOfMember is blank it would show nothing.

If the SignInMemberID doesnt exist but the GuestOfMember does then it would show the First and Last name beside that field.

If the number doesnt exist it would be blank.

I entered the places I wanted to show the data in the query for your reference as expr1, expr2, expr3, expr4 for your reference.

Any help would be appreciatted.

View 1 Replies View Related

Query Trouble!

Oct 4, 2004

I have set up a query to return the records of pupils with certain information, most of this information comes from a subform, (i have set up a form with multiple subforms to make data entry easier for the user) with these subforms many records have multiple subform records (i.e. Joe Bloggs has 3 different Approved hosts and 3 approved drivers) when i try to make a report it wants to print the same data 9 times (because there are 9 differnet combinations i asume) what i want to to is have the approved drivers in one list box and the approved hosts in another list box and have the report print only one!

can anyone help me!

please!

View 1 Replies View Related

Exclusionary Query Trouble

Feb 8, 2006

Trying to create this thing is tying my brain in knots, so I'm back for help. Hopefully I'm missing something basic:I've finally been able to normalize my database by taking it offline for a few weeks, and already the amount of duplicative/erroneous data that has been cleaned up is staggering. Right now I'm trying to ensure that our data is as complete as possible in the following circumstance: tableGroups has PK GroupID tablePeople has PK PersonID tableRelationships creates a many-to-many relationship between the two, via PersonIDLookup and GroupIDLookup. It also has its own Autonumber PK. tableRelationships also has fields that describe the relationship: For example, FormerRelationship, MailingContact, and PublicContact are all boolean fields. What I want to do is get a list of all GroupIDs which do not have any entry that is marked PublicContact. Problem is that some may have Former contacts, or privately listed ones for internal office use only; over time our Public Contacts move, die, quit, etc and sometimes they have not gotten replaced. I'll need to do the same thing for MailingContact as well, but the procedure for one should be the same as the other. No one who is a Former contact should also be listed as Public/Mailing, but I can't promise the data is that clean at this point.The only thought that occurs to me right now is to make one query that shows GroupIDs that do have at least one matching relationship, and then create a Find Unmatched query that relates to that. Is there something simpler with only one query?

View 3 Replies View Related

Trouble With Crosstab Query

Feb 16, 2006

This is my first time posting here and I hope you guys can help me.


I have a problem with using a crosstab query.

My original table looks something like this

VALUES
Oil_ID (PK)
Property_ID (PK)
Col_Heading (PK)
Row_Heading (PK)
Values
Oil Name
Property Name

I made a crosstab query with the columns being 'Col_Heading' and the rows being 'Row_Heading' and the values being 'Values'.

I made a form that has two combo boxes (Oil Name, Property Name). It also has a subform in which I want to display the crosstab query.

Now here comes the problem. Those two combo boxes need to be able to filter the data, but I am not able to put the code:

IIf(IsNull([Forms]![Values Form]![combo52]),[VALUES].[Oil Name],[Forms]![Values Form]![combo52])

IIf(IsNull([Forms]![Values Form]![combo66]),[VALUES].[Property Name],[Forms]![Values Form]![combo66])

in the Criteria because an error pops up saying: "The Microsoft Jet database engine does not recognize 'above_code' as a valid field name or expression.

So I created a select query, call it QueryA', that would let me filter the data. The I created a crosstab query based on QueryA, but the same error message occurs.

I am stuck. If you could help me, I would greatly appreciate it.

Thanks

View 1 Replies View Related

Trouble With A Union Query

Nov 20, 2006

I have a union query that will run from the SQL design window but if I try and run it from the database window it crashes Access (2003). Any ideas?
SELECT [TestLat]-0.5 AS Lat, tblTestData.TestLong AS [Long], tblTestData.TestTemp
FROM tblTestData

UNION SELECT [TestLat]+0.5 AS Lat, tblTestData.TestLong AS [Long], tblTestData.TestTemp
FROM tblTestData

UNION SELECT tblTestData.TestLat AS Lat, [TestLong]-0.5 AS [Long], tblTestData.TestTemp
FROM tblTestData

UNION SELECT tblTestData.TestLat AS Lat, [TestLong]+0.5 AS [Long], tblTestData.TestTemp
FROM tblTestData;

View 4 Replies View Related

Having Trouble With OR Operator In Query

Nov 11, 2007

Hello everybody,

I am having trouble with this criteria on one field in a query

<>19 Or <>15 Or <>14 Or <>16 Or <>17 Or <>1 Or <>3

The query is still showing records that contain 14, 3 and 16.
Basically, I don't want to show records containing 19, 15, 14, 16, 17, 1 and 3.

Thanks

View 3 Replies View Related

Trouble Writing Query Value To Form

Feb 10, 2006

I have a query called VOLTEST that is not bound to the form. VOLTEST sums a field in a table from a subform. When the input is finished in the subform and control goes back to the main form, I want the total from the VOLTEST to show in a field on the form that is not a tab stop. Basically, this is just for reference.
I have tried to put =[VOLTEST]![SumOftest_value] in the control source of the field on the form but I keep getting "Name?" as the result instead of the sum total.
I can run the query in the gotfocus of the first field in the tab order after the subform and I get the correct result in a datasheet view. So, I know the query is working correctly. I just need it to show the result in field on the form.

Thanks for your help. GG

View 2 Replies View Related

Having Trouble With Date Range Query

Feb 2, 2007

I am creating a database for a workplace for staff management.
i want a query where the user can input a month or a date range and be able to view all of the staff on holiday during this period.
I originally had a working query however it ignored staff that were on holiday during this period for example searched 11/2006
showed holidays in november but not staff who are on holiday from before and still on holiday during this time.
At this moment in time the fields i have included for beginning the query is BookingID, StaffID, EventName,StartDate,EndDate


any help would be appreciated

View 1 Replies View Related

Trouble With Between Criteria In Parameter Query

Oct 8, 2007

In MS Access 97 I have a basic query based on a single table. When I use "Between [Start Date] and [End Date]" as the criteria in a date field it returns incorrect records.

It works correctly on date fields that are not calculated but not on the date field that is calculated. If I type in the actual dates in the criteria (instead of the prompts) it returns the correct records.

Is there a solution to this behaviour? I need the users to be able to input the dates.

Thanks.

P.S. I have tried using the base query as a subquery but I still have the same issue in the second query based on the subquery.

View 5 Replies View Related

Trouble Flipping Name Field In Query!!!!

Oct 9, 2007

:mad:Hello I'm new to queries!

I'm having a problem writing an update query that will look at a name field ie (Dan Wilson Sr) and flip the name to a shortname ie (Wilson Sr Dan) with the criteria based to only flip the name if it contains Jr or Sr.
Below is an example of a select query I was using to test before updating my table.

SELECT *
FROM [Goodrec - All]
WHERE (instr(shortname,' JR')>0 Or instr(shortname,'SR')>0 Or instr(shortname,' III')>0) And instr(shortname,' MD')=0;

This query provides the following result for example, if name is "PAUL D TAYLOR SR" it is flipped to "SR PAUL D TAYLOR." I need shortname to display it like this "TAYLOR SR PAUL D"

Thanks in advance for any assistance!

View 14 Replies View Related

Queries :: Combine Two Query Sql Code To Make Only One Query

Apr 5, 2013

First query = Sum Products:

Code:
SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;

Second query :

Code:
SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM [Sum Products]
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));

I need to combine those two query sql code to make only one query.

View 3 Replies View Related

SQL Code Within Query

Sep 30, 2005

I have this code from a Query that works.

SELECT Contacts.*,Contacts.Address1
FROM Contacts
WHERE (((Contacts.Address1) like "*" * (Enter 1st Line of Address here) & "*") AND ((Contacts.Site) = "Miscellaneous"))
ORDER BY Contacts.Site;

I want to replace the data "Miscellaneous" with a Public variable strSite.

If you can help I would be very happy and of course grateful.

Thanks

Ted :confused:

View 7 Replies View Related

Query Or Code?

Jan 8, 2008

I have been asked to help with a database that someone is writing.

He has a Table that has 200+ columns and approximately 70 of those columns are Qualifications. The first few columns are Title, Forename, Surname.

What he is trying to do is query the table to find all people with a specific Qualification and that is a Mr.

If I had written the db from the start then I would not have produced so many columns for qualifications. Instead I would have 1 column for quals and the populate each record in this way.

My question to the forum is the following:

With the current structure of his table (200+ Columns) Is there a way that I can produce a Query that will find the results that he requires, 1 specific Qualification and Title, but without displaying all other details in the table?

I understand that I can choose what field to include in the query but what I really do not with to do, is produce 70 different queries in order to filter each qualification.

Any and all help with this question really is appreciated.

Thank you

Bev

View 5 Replies View Related

Writing Code For Query

May 31, 2006

Hi

I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.

I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.

Help me in writing this code.

Thanks in Advance Very much
Deepak

View 1 Replies View Related

Writing Code For Query

May 31, 2006

Hi

I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.

I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.

Help me in writing this code.

Thanks in Advance Very much
Deepak

View 1 Replies View Related

Help To Combine Code In Query.

Jul 24, 2005

I am using the two lines of code below Each in separate columns of a query (or two text Boxes on a form) to calculate the elapsed time between two dates formatted as General Dates. The first line of code (in column A, "txtTime1") calculates to the total minutes between the two dates, the second line of code (in column B, "txtTime2") takes the minutes and converts them to total elapsed hours and minutes. They work great, however, I would like to combine this code and place it in only one query column or form Text Box. My VBA is not up to it, can someone out there show me how to combine the code below to produce the hours and minutes in one line of code? Thanks

=DateDiff("n",[StartDate],[CompleteDate])

=[txtTime1]60 & Format([txtTime1] Mod 60,":00") :confused:

View 2 Replies View Related

Writing Code For Query

May 31, 2006

Hi

I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.

I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.

Help me in writing this code.

Thanks in Advance Very much
Deepak

View 2 Replies View Related

Query Code In VBA - Newbie

Oct 5, 2006

In the code of a button, ive got....

Which should be pulling the value in cb1 in the last cat query, but im getting an error saying it cant find the field "|" refered too...

Basically there is a table called Last Cat, and I want to draw the value of cb1 out of it where the catpathid = 5 so i made the query to do that... but now im abit lost....

Me.combo1.Value = [Last Cat Query1]![cb1]

any help would be awesome.

View 1 Replies View Related

SQL Code For Select Query

Nov 23, 2006

Im looking to have a query that selects the "NAME" from a table "tblPeople" where the NAME field begins with "A". Ive tried using WHERE tblPeople.NAME Like "A" with no luck.

Can anyone help me out, im sure im missing something really simple.

View 1 Replies View Related

Create A Query In Code

Dec 7, 2006

How do you create a query in code. Actually creating a query that appears under the query section of access. Is this possible?


Thank you

View 2 Replies View Related

Displaying Query By Code

Mar 8, 2007

I need to display the results of a query on screen when somebody clicks a button on the form. So I put code in the event of the click that looks like this:

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim stDocName As String

stDocName = "HHC Report"
DoCmd.OpenQuery stDocName, acPreview

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub

So far, so good, but my problem is that this query is one row with a lot of columns - that creates a lot of pages to look through. Is there a command that would loop it around on the same page, or rotate it clockwise so it's going down the page instead of across?

View 10 Replies View Related

VBA Code And Query Criteria

Jan 14, 2008

OK I have worked in Access for awhile now but never worked with SQL statements or anything like that. This is what I am trying to do I want a criteria in a query come from a public string that I have. When some one logs in to the database it stores what property they work for I only want records to show up for that property. I don’t want them to have to type in there property code again every time the query is run. What do I need to do to get this to work? Any help would be greatly appreciated. Thanks

View 1 Replies View Related







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