Extra Lines Appearing In Query Builder!

Jul 1, 2006

Can anyone help please?

I have a search form that passes 4 parameterers to a query.
In the query builder it reads:

Like [FORMS].[frmCourseSearch].[txtCourse] & "*"
>=[FORMS].[frmCourseSearch].[txtStartDate]
<=[FORMS].[frmCourseSearch].[txtEndDate]
[FORMS].[frmCourseSearch].[txtCourseID]

This works fine. But I also want it to search on only 1 or 2 parameters and set the rest to Null so I have used this statement for each one.

Like [FORMS].[frmCourseSearch].[txtCourse] & "*" OR Like [FORMS].[frmCourseSearch].[txtCourse] & "*" Is Null

This again works ok and I can search on any parameter. However, every time I enter data into the search form and run the query it repeats the query string in the query builder. i.e

Like [FORMS].[frmCourseSearch].[txtCourse] & "*"

Like [FORMS].[frmCourseSearch].[txtCourse] & "*"

Like [FORMS].[frmCourseSearch].[txtCourse] & "*"

etc...

It does this with all 4 parameters and it's starting to look very messy. The SQL view is the same with a new repeated line added each time I run the query.

How do I stop this happening?

Thanks in advance.

View Replies


ADVERTISEMENT

Reports :: Extra Blank Page Appearing In Report After Modification

Sep 11, 2013

I have been modifying an existing report that was formatting and printing fine but since I modified it (adding some text boxes and moving some controls around), it is now adding a blank page.

For example, If the report was normally 2 pages long with page numbers, it is inserting a blank page without page number between page 1 and page 2......

View 4 Replies View Related

Reports :: How To Get Rid Of The Lines Appearing In A Report

Jul 13, 2013

how I get rid of the lines appearing in a report? I've set everything to transparent but the group header and subsequent records in the subreport all have separating lines.

View 7 Replies View Related

General :: Export Table To CSV And Then Add Extra Data Lines At Header And Footer Of File?

Feb 20, 2014

Managed to export a table to csv file. The problem I now have is I need to add the following Text at the start of the file:-

H,LKJ85485524,DE

and the new last line :- T,whatever the record count is,2

Whether this info is added before or after export is not important.

I have even resorted to using VBA to create an Excel spreadsheet and saving to csv.

Thought this had cracked it however I had surplus commas at head and footer where the original csv file had additional columns (ok manually delete these comas) ,then found that the end of each csv line was missing a final comma that is needed by the recipient of the file.

View 4 Replies View Related

Forms :: Blank Form Appearing When Query Returns No Record

Mar 24, 2013

When a query returns no records, the form appears blank. How can I make the form appear eventhough there are no records to show prior to inputting data.

View 5 Replies View Related

Question About Extra Text In Query

May 25, 2006

Hello,

I made an database with all addresses.
When persons live in an appartment, then the field "busnummer" will be filled in with a number. When they live not in an appartment, the field "busnummer" is empty.

In a query, I made an expression and filled the expressien in field (in the query):

Expr3: IIf([Busnummer]>0;"bus [Busnummer]";"")

When "Busnummer" is greater then 0 THEN write the text "bus" and the value of the field right behind, else leave the field empty.

The result of the expression above is:
"bus [Busnummer]"

I have already tried the following:
Expr3: IIf([Busnummer]>0;[Busnummer];"")

The result of the expression is the number I filled in in the database. Now I want to have the result: bus and the number I filled in... :)

Who can give me a tip to solve this problem?


Kindly regards

View 3 Replies View Related

Unwanted Extra Results From Select Query

Mar 9, 2006

Hi,

I am a novice with Access and would like a steer with what I am sure is a simple issue but I can't find an answer. :confused:
I currently have 2 queries based on 2 separate tables.

Ops_Log_996_Query
SELECT [996_Table].Unit, [996_Table].Location, [996_Table].hiredate
FROM 996_Table
WHERE ((([996_Table].hiredate)=Date()));

Ops_Log_SQTU_Query
SELECT SQTU_Table.Unit, SQTU_Table.Location, SQTU_Table.hiredate
FROM SQTU_Table
WHERE (((SQTU_Table.hiredate)=Date()));


When run separately the first query returns 2 results and the other 1 result - fine so far.
I am now trying to combine the results for display in a report so I have a third query which takes its info from the first two -

SELECT DISTINCTROW Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate
FROM Ops_Log_996_Query, Ops_Log_SQTU_Query
GROUP BY Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate;

This displays the 2 separate records in the first 3 columns ok but in the last 3 columns the info in record 2 is a repeat of record 1. :confused:

Can anyone help me out please ?
Thanks, Oscar

View 2 Replies View Related

Syntax Error: Extra) In Query Expression

Oct 11, 2007

Hello I'm having trouble getting my nested Iif statement to run. Can anyone help??? I've attached a screen shot of the syntax error that I'm receiving. The example code below needs tweaking.



Update [Goodrec-copy3] set [Goodrec-copy3].shortname = Iif (Not Null([shortname]),[Goodrec-copy3].shortname Like "*,JR*" Or ([Goodrec-copy3].[shortname] Like "*, SR*" Or ([Goodrec-copy3].[shortname]) Like "*, II*" Or [Goodrec-copy3].[shortname]) Like "*, III*", InStrRev([shortname])," ",InStr([shortname]," ,")+1,50) &" "& Left([shortname],InStrRev([shortname])," ,")-1) WHERE ((([Goodrec-copy3].[ctype])="I"));

What I'm attempting to accomplish with this query is to keep the field shortname the same if not null and if it doesn't meet the criteria of having a string value of "JR", "SR", "II", or "III". If the field does have a string value of "JR", "SR", "II", or "III" reverse the string (example John Gissom JR) to reflect shortname as such for example: "Gissom JR John".

Thanks in advance!

View 2 Replies View Related

Update Query Creating Extra Record

Jan 5, 2005

i have this problem that is bugging the crud out of me:
sql="UPDATE bedrifter SET pr=" & Request.Form("pr") & ",totalindexedpages=" & Request.Form("tip") & ",totalinboundlinks=" & Request.Form("til") & ",description='" & Request.Form("dsc") & "' WHERE created='" & Request.Form("ts") & "'"

conn.Open connStr
conn.Execute(sql)
conn.close()
Set conn = nothing

when i run this code it updates the correct record (line in my access db) but then it also adds a new line with only that info in the update query. why is it doing this? when i update using the ID instead of using the timestamp in the WHERE clause it works fine. really frustrated...

View 1 Replies View Related

Between Dates Parameter Query - Extra Records Generated??

Apr 4, 2008

Good Evening Everybody,
I am currently helping some people out at work with their database. My knowledge of Access is very limited, and whilst my it is improving I have nonetheless come up against a ‘hurdle’ which I am struggling to jump. The database in question is not complex, quite the opposite in fact. Basically 1 table ‘Main Data’ , 1 data entry form and a few reports. The table includes 8 fields in the following formats:

ID: Auto Number
Area: Text
Equipment:Text
EquipSrlNo:Text
EquipLocalNo:Text
MOPNo:Number
DateDone:Date/Time
MOPPeriodicity:Text


I was recently asked to produce a report for them that would search between two dates using a parameter query and to then return the result. The problem is that when I generate it using the following SQL it returns records in the period that I asked for, but then collects ‘additional records’ which fall outside the date-span that I originally requested, i.e. search Between 03/04/08 And 30/04/08 produces data that relates to data in May, June, July 08 etc. I thought that what I was doing was correct, and it has worked for me in the past- but on this occasion I am absolutely confused???

SQL
SELECT [Main Data].Area, [Main Data].Equipment, [Main Data].EquipLocalNo, [Main Data].MOPNo, [Main Data].DateDone, [Main Data].EquipSrlNo, IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone]))))) AS TestDueNext, [Main Data].MOPPeriodicity
FROM [Main Data]
WHERE ((([Main Data].Area)=[Enter Area Type]) AND ((IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone])))))) Between [Enter Date Start] And [Enter DateStop]));

Unfortunately, I do not have a copy of the database in question and so I am unable to post it for the purpose of explanation.

Can some kind person point me in the right direction as I am slowly losing the ‘will to live’ Ha? Ha? You have always been so helpful in the past and hence the reason I have called upon my ‘forum friends’ to help me out?


Look forward to your response(s)


Best Regards

CarolW

View 4 Replies View Related

Error In Query In Expression Builder...

Sep 21, 2006

I need to implement the following logic in a report column:

IF hist_mis_cds!typgess = "", then calculated field= ""
ELSE
IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc
then calculated field= "X"
ELSE calculated field= ""

I have implemented the following query in Expression Builder for the calculated field:

=IIf(HIST_MIS_CDS!TYPGESS="","",IIf(HIST_MIS_CDS!T YPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X","" ))

When I click on Form View, I get "#Name?" in the calculated field.

Any ideas? Thanks.

View 10 Replies View Related

Error In Query In Expression Builder...

Sep 21, 2006

I need to implement the following logic in a report column:

IF hist_mis_cds!typgess = "", then calculated field= ""
ELSE
IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc
then calculated field= "X"
ELSE calculated field= ""

I have implemented the following query in Expression Builder for the calculated field:

=IIf(HIST_MIS_CDS!TYPGESS="","",IIf(HIST_MIS_CDS!T YPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X","" ))

When I click on Form View, I get "#Name?" in the calculated field.

Any ideas? Thanks.

View 12 Replies View Related

Query Fields Not Showing In Expression Builder Or Elsewhere

Mar 14, 2008

In Expression Builder and other areas of Access where the fields of a query show, my fields are not showing anymore for any of my queries. Does anyone have a solution for this?

Thanks.

View 3 Replies View Related

Can A Button On A Form Open The Query Builder?

Oct 2, 2004

Hi

I'm not sure if this is a dumb question or not but is it possible for me to have a button on a form that will open the Query Builder?

I have a form that has 3 buttons on it - 1 to access records, 1 to access reports and another which I would like to open the Query Builder when clicked. The database will be used by beginners so I've hidden the design view button and also the database window which appears at startup. All that appears when I open my database is the 3-button form which is what I want.

I thought maybe I could save a blank query and then create a macro to open that whenever the button was clicked and stipulate in the macro to change the view to design view but that didn't work.

Thanks to any help or advice anyone can give and sorry if it turns out to be a really stupid simple solution I've missed (I'm good at that!)

View 1 Replies View Related

Queries :: How To Use OR Condition In Query Builder Wizard

Aug 19, 2014

I have to three fields in my query wizard, lets call them A and B and C. The condition for the query is either A or B is Null and C is Null. Im not sure how to do this though becuase A, B and C are different fields. I wrote

Code:
Is Null

.

under the criteria of A and B and C, but this specifies that A,b and C is Null, how can I make it so that C must be Null and either A or B or Both.

View 1 Replies View Related

Queries :: Combining Data In Access Query Using Builder Function

May 13, 2014

I have a table in Access 2010 and in one field i have multiple records of the same data as in the next field it has unique data for example:

NameColour
CarBlue
CarGreen
CarYellow
BusOrange
BusPurple
BusRed

I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:

NameColour
CarBlue;Green;Yellow

How i would do this.

View 5 Replies View Related

Count Of Query Lines

Mar 12, 2007

In a query, how do I do a count of the number of lines that appear in the query? Not the number of records, because the query itself is grouped. The results are in order, highest to lowest, and I want to see just the top 50.

View 3 Replies View Related

Numbering Lines In A Query

Mar 13, 2007

How do I number the lines in a query? I have a 'Top 50' query, and I want to number the lines 1 through 50.

View 6 Replies View Related

Queries :: Updating Lines In A Query

Sep 26, 2013

I have created a query which is based on 2 tables.I now want to use the query to update content as needed in one of the tablesWhen I try to do this it says, Operation must use an updateable query..I used the update feature but it still says must be created in updateable query.

View 4 Replies View Related

Help With Select Query To Get Single Line Instead Of Multiple Lines

Dec 21, 2007

Hi,

I have a query that selects values from a table and I'm further creating a letter based on this query. The problem i'm having is that if the query pulls 3 rows as result then it creates 3 separate letters. Basically this is how the output of query looks:

(refer attachment)

Is there any way to write this query so that I can get output as below:

Col1 Col2 Col3
10 20 10


Please advise.

View 7 Replies View Related

Forms :: Duplicate Lines In Multiple Form And Query

Jul 8, 2015

The purpose of my database is to keep a record of training activities undertaken by staff. There is therefore a multiple form which displays, for a specified worker, the list of all the required activities. Each line has a button which opens another form to allow an activity to be started (together with start date and other info). The main tables are Worker, Activities, and a junction table Worker2Activities. On closing the second form and refreshing the first one, the started activity is correctly recorded, but on as many lines as there are staff on the course. To make matters worse, these also appear when i then select another worker (and if i create another worker, the number of lines goes up by one for all of them). The Worker2Activities table correctly show one new record created

View 3 Replies View Related

General :: Export Table / Query - Two Lines Per Record With Different Specs?

Nov 11, 2014

I have two tables that need to be exported on one .txt file.

One table has the header record. And the 2nd table has the detail record.

My issue is as follows:
1. Need to create one .txt file
2. The header record has one set of export specs and the detail record has a different set of export specs.
3. The first line is the header record, and 2nd line is the detail record.

Example below:
H Abraham Armenta January Cake 00123
D Father Saturday 02536 00123

Both tables have a common control number - in this case represented by the 00123. I just cannot figure out how to accomplish this..

View 2 Replies View Related

Queries :: Date Range Query - Return Lines Where Field Is Blank In Table

Aug 15, 2013

I have a single table with customer information, one of the fields is a date field "LastContacted".

I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.

I have written this using Nz so that it can still return results if the search boxes are left blank:

Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)

This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?

I have tried:

like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"

but this returns errors when I try to run it.

I'm using Access 2010.

View 14 Replies View Related

Query Or Query Builder

Feb 7, 2006

Which has higher performance at run time, saved queries or using query builder on a form?

View 9 Replies View Related

Control Is Appearing On More Then 1 Tab

Oct 2, 2005

Hi there

I have a form that has 4 tabs on it.

When I add a control onto one of the tabs, The control is appearing on all 4 tabs.

What causes this?

Thank you for your help!!

View 6 Replies View Related

Values Not Appearing In Table

Mar 10, 2005

Hey PPL...hows life???

i need some help here.... One of my forms has an age text box which is linked to a module ... so however, it calculates the age......

But the age is not shown in the table.... I need it to do that....

But i dont know which code to use... I heard of the INSERT INTO function
but dont know how to use it and under which event to put it....

my textbox in the form is named Age and the table is TblPatientDetails???

Please i need to do this urgently...

Thanx

View 1 Replies View Related







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