Parameter Query With An Embedded Quote String Problem

Sep 19, 2006

Hi Folks,
I'm sorry to be stupid, here, but I can't get this to work. I have a parameter query that forms the basis for a report. To get the value for the query, I have a form on which the user selects the value they want from a combo box (cboProgram). The values in the combo box include all of the programs used in the file plus a value called "All" which means - just include all the records. My problem is that when "All" is selected in the combo box, no records are returned.

Things to mention:
"All" has a value of 1 in the lookup table.
I tried the query with the actual values and it works fine. The problem is when I try to do it from the combo box.

My first attempt was to put:
iif([forms]![frmFormName]![cboPrograms]=1, "*", [forms]![frmFormName]![cboPrograms]) in the criteria for that field in the criteria box for lngPrograms.

Access' response was that the criteria value was too complicated to resolve.

So, I noticed that I wasn't saying 'Like "*"' in the first part, but if that's the problem then I can't get the numbers of quotes right. I also tried switching the iif statement around and saying <>1,[forms]..., else some version of 'Like "*", but that didn't work either.

Then I queried around the forum and found a recommendation to make a function to return the value, so I did the following:

In the criteria for the field in the query:
IfAll([forms]![frmFormName]![cboPrograms])

And in a module:

Public Function IfAll(ProgSel)
Select Case ProgSel
Case 1
IfAll = "Like ""*"""
Case Else
IfAll = ProgSel
End Select
End Function

I'm still not getting it. If "All" is selected, value = 1, then no recorsd are returned. Can anyone help me out please?

Gratefully,
-D

View Replies


ADVERTISEMENT

Queries :: OR Using Part Of String From Parameter Box

Aug 1, 2014

I have a criteria in a parameter query that asks the user to enter a Subsystem number (such as 4596-666-001).
The source data for my database has Systems (e.g, 4596-666) and each system has several Subsystems associated with it (e.g, 4596-666-001, 4596-666-002, etc). The System and Subsystem numbers are stored in the same column of the source data spreadsheet, and hence the same field in my database.

The source data is structured in such a way that Systems and Subsystems have different information. I would like to set up a query that shows the System information as well as the Subsystem information.

For example, if the user enters 4596-666-001 into the parameter dialog box the query returns all the information for 4596-666-001 and 4596-666 (but NOT any other Subsystem).

As a work around I have set up an OR situation where the user first enters the Subsystem number and then another parameter query dialog opens and they enter the System number. This works but is not as nice as simply entering the Subsystem number and having all information returned.

Is there a way to set up my query so the when 4596-666-001 is entered an OR is generated using the first 8 characters of the Subsystem number?

View 1 Replies View Related

Queries :: String In Expression Is Being Treated As A Parameter?

Apr 30, 2014

I've got a query. One field is an expression on which I'm filtering.

A shortened version of the expression is
Expr1: IIf([Status]=Active,"Yes","No")

There are other parts to the formula, hence why I'm not filtering [Status] itself, but stripping them out gives the same problem.I'm filtering on "Yes".What is happening is when I close the Expression builder, it changes "Active" to a parameter, thusly:-

Expr1: IIf([Status]=[Active],"Yes","No")

Status is a text field, it's a query on a single table, there's no field called Active.

View 4 Replies View Related

Queries :: Limit On Embedded IF Statement In Query

May 23, 2014

I have the following code

Code:
Gap: IIf([Q1]=2,"GAP",IIf([Q2]=2,"GAP",IIf([Q3]=2,"GAP",IIf([Q4]=2,"GAP",IIf([Q5]=2,"GAP",IIf([Q6]=2,"GAP",
IIf([Q7]=2,"GAP",IIf([Q8]=2,"GAP",IIf([Q9]=2,"GAP",IIf([Q10]=2,"GAP",IIf([Q11]=2,"GAP",IIf([Q12]=2,"GAP",
IIf([Q13]=2,"GAP",IIf([Q14]=2,"GAP",""))))))))))))))

I need to add Q15 and Q16 but there appears to be a limit on how many IIf statements I can embed and I get an error that my formula is too complex.

View 3 Replies View Related

Quote Generator Help

Feb 28, 2006

I've been asked if I can create a database which will act as a quote generator.
There will be 5 components to make up the whole product. However, the options available for components 3, 4 and 5 will be dependant on the components chosen for 1 and 2.
i.e. there may be 5 options for each component.
If part 1a is chosen, then only parts 2c, 2d and 2e will be available. If part 1a and 2c are chosen, then only parts 3a and 3d are available and 4b, 4d and 4e are available.
...does any of this make sense?????

My question is... can anyone tell me how I should create the tables and the relationships to enable me to create a database like this. I am completely stumped.
It may be that this is completley beyond me, but i'd like to at least be able to give it a try.
Any help would be appreciated.
Thanks

View 3 Replies View Related

Quote Form

Feb 17, 2005

I have been trying to create a Quote form in my Database. I have a list of products, each product then has a table that details prices based on quantity breaks (i.e 500 to 999 and 1000 to 1999 etc.).

In my Form I am unable to create a subform in datasheet view that will enable me to select a product from a combo, type in a quantity and for the price to be displayed based on the quantity?

Has anyone managed to achieve this? I would love to see the code and put it into practice.

Many thanks

Nik

View 1 Replies View Related

Capturing Parameter From Parameter Query

Jul 12, 2005

I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.

View 2 Replies View Related

How To Eliminate A Double Quote

Nov 13, 2007

I have a string with one or more double quotes in it. How can I remove the double quotes? (I was able to remove single quotes.)

Example: Purchase "other items". (This the data in a column.)

I want to change it to Purchase other items

View 3 Replies View Related

Single Quote Glitch

Jan 6, 2005

Hi,

When trying to update an access membership db where expired members are
being set to 'inactive' the script keeps hanging up with a runtime error
when the first or last name of the member
has a single quote in their name (eg O'Neil)

Any ideas where to look to fix this would be appreciated.

Regards
Justin

View 3 Replies View Related

Single Quote Dilemma

Aug 31, 2003

i have an error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Products.Catalogs LIKE '%[a-z]%' AND (Products.ItemName LIKE '%t'ai%') AND ((Products.NewItem = True) OR (Products.HotBuy = True)) ORDER BY Products.ItemName'.
/webdev/flaghouse/NEWITEM_List_Main.asp, line 149

this results when search terms contain a ' (SINGLE QUOTE). i've tried to double up all of the SINGLE QUOTES in the code and still get an error.

heres the CODE:

' check if keywords entered, split comma-delimited list into array of keywords

If Request("Keyword") > " " Then
theKeyword = Request("Keyword")
listKeywords = split(theKeyword,",")
maxCounter = ubound(listKeywords)
whereClause = "Products.ItemName LIKE '%" & listKeywords(0) & "%'"
FOR counter=1 TO maxCounter
theKeyword = listKeywords(counter)
whereClause = whereClause & " OR Products.ItemName LIKE '%" & theKeyword & "%'"
NEXT

SQLString = SQLString & " AND ("
SQLString = SQLString & whereClause & ")"
Else
theKeyword = "None"
End If


if anyone sees anything amiss, please let me know. i'm at wit's end!!

thanks. please email me at: URL. again, thank you in advance for your help.

kwc

View 5 Replies View Related

Quote/Invoice Access Database

Aug 25, 2004

Hi

I am after a MS Access Database that will allow me to do up quotes and invoices for my small computer business does anyone know of any free access databases that will allow me to do this

If you know of any can you please let me know via email matthew01@gmail.com

Thanks

Matthew

View 1 Replies View Related

Designing A Proposal/Quote Reporting Database

Feb 2, 2006

As a basic user of Access I believe I have identified a use for the program for the purposes of a reporting task which I am charged with at the moment. We undertake the weekly tracking of proposals, which involves in brief, the action required for the bigger proposals, the teams involved in writing the proposals and follow up information as well as registration of wins and losses etc.
At the moment we use Excel but I am finding it extremely labour intensive with entries having to be removed and captured by only one person on a weekly basis and when you are talking about 40 to 50 new proposals registered nationally in a week and about 100 updates on existing jobs it becomes a perpetual case of chasing your tail.

Am I right in thinking that if we were to design a simple, effective database with a form design that all our research directors could use with ease, and we could merely run reports off each week that this would not produce a far quicker reaction time when it comes to pooling international resources but also far more effective reporting system with which to produce our management summaries?

I am aware that this will probably require quite a bit of consultation on design but before I go shouting the merits of Access to any of my superiors I wanted to make sure I could be confident of Access's capabilities first?

Can anyone advise? point me in the right direction?

Many thanks!

Lisa H.:confused:

View 2 Replies View Related

Remove Quote Marks From Imported Table

Mar 16, 2008

I am using DoCmd.TransferText to import a CSV file to a new table.
The problem is some of the fields contain quotation marks which really messes things up for me later on.
Is there some way I can go through the whole table and remove them?

Maybe a Query?
Or VBA?

View 9 Replies View Related

Queries :: Isolating A Field With Quote Marks?

Oct 24, 2013

I am trying to isolate a field with quote marks (").

WHERE (((dbo_AC_ECONOMIC.SECTION)="));

but that does not work..

View 2 Replies View Related

Table Layout For Airport Minicab Quote System

Jan 3, 2006

ok basically im creating a quote system that will show users prices from one destination to the other destination based on the size of the vehicle..

you can see a similar quote system used on the following website..
http://www.londonairporttaxi.co.uk

there will be three main tables..

Airport Table
airportID - primary key, autonumber
airportNAME - text

the airport table will consist of 5 records..Heathrow,Gatwick,Stansted,London City, Luton

Town Table
TownID - primary key, autonumber
TownNAME - text

the town table will contain a list of postcodes, maybe 50 or so (n1,n2,n3,n4,e1,e2...)

Car Table
CarID - primary key, autonumber
CarNAME - text

the car table will consist of 4 records..Saloon,Estate,MPV,Executive

now what i need to do is create a similar quote system like there is on the url above..

my original idea would be to do something like this..

Have three tables that contain the quotes for each type of car..the first two listed here will work i think..


Airport2TownQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
townID - the town Destination, foreign key
price - currency

Town2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
townID - the town Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency

however.. for airport to airport quotes.. the resembling table would look something like this..

Airport2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency

but you cannot place the airport id twice in the same table..(this maybe a case of simply renaming the airport fields in this table such as airport1, and airport2.. but can you still link them and enforce integrity if the fields have different names?)

also one example may be Heathrow to n1 is £30... this does not mean however that n1 to heathrow will be £30.. this is why i need to separate the quotes in this way.. for the 3 possible journeys (airport to airport, town to airport, airport to town).. also the quotes will be different for each car type..

anyway.. i shall be using asp to create a similar quote system on my website.. but i just need to get the final layout of the database sorted.

thanks in advance for any help.

View 7 Replies View Related

Running Average Quote Based On Part Number

Apr 28, 2005

I've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.

On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?

If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.

View 3 Replies View Related

General :: Create A Form That Allows To Search For Particular Quote Numbers

Jul 8, 2013

I am creating an access database to store customer details as well as quote information. I have created a form to input quote details that our office can complete when a customer calls to complete a quote. I need to create a form that opens as a new form every time we click on the form to input a new quotation. How do I do this? At the moment when I click on the form, the form opens but the details of the last quotation are stored on the open form.

I have no training in access at all and am self taught so far so by no means an expert.I also want to create a form that allows you to search for particular quote numbers, so if someone calls and asks about a particular quote number we click on a page that says 'search quotes', input the quote number and the form opens with all the details of that quote.

View 2 Replies View Related

Queries :: How To Insert Comments And Notes Using Singular Quote

Feb 5, 2015

I know that you can insert comments and notes in VBA code using a singular quote, like the following:

Code:
'Comment about what the code does

Is there a similar way to insert comments in the SQL of queries? I found other forum posts that say this isn't possible but those were all for pre-2010 Access. Has this changed at all?

View 5 Replies View Related

Parameter Query Asking For Parameter More Than Once!

Nov 9, 2006

Hi,

I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!

Here is my code (abbreviated slightly):

SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);

I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.

So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!

Help! :confused:

P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.

View 2 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

Embedded Audio Question

Jul 14, 2005

Ladies and Gentlemen,

I am needing to develop an audio catalog and would like to know if anyone has experience with embedding audio in an Access database. The ideal situation would be to have all audio contained within the database, however, some of the audio files may be quite large and I don't want to bog down the entire database by having 100MB files in there either.

So I guess my question simply is; if my audio files are 50MB or larger, embed or link?

Thanks for any advice.

View 2 Replies View Related

Embedded Ole Object Disappears

Jun 28, 2007

In the form view of the db I have a bound box for an embedded ole object
I use it to embed Word documents for each db entry (The database is for works of art and the Word document is the description of each work of art which often contains Chinese Characters, hence the Word Document)

The box is set to show an icon (The Word Icon)
Upon entering the data and moving off the record, subsequent return to to the record sometimes shows that the icon is gone from the box, it is blank.
Clicking the blank box brings up the Word Document but then closing the Word Document and re-clicking the blank box dos not bring up the Word Document.
The standard message saying that the ole object is empty appears and all the data from the Word Document is lost.

this has only happened since moving to Office 2007. I upgraded the db to 2007 format and we are using Word 2007.

A Google of the symptoms doesn't bring any meaningful results, does anyone have any ideas?

I tried to keep this as short as possible (which wasn't easy) if you need more info just ask

Thanks

View 3 Replies View Related

Embedded Excel OLE - Need To ZOOM!

Sep 29, 2005

I have an embedded excel workbook on an Access form (Access 2002)

I would like to change the zoom of the excel "view".

I've tried this:

' xlCharts is an embedded XL workbook
xlCharts.Object.Parent.ActiveWindow.Zoom = 100
I get an error that says:
"Unable to set the Zoom property of the Window class"

However, if I READ the zoom level, it works fine. The zoom property of the window class in excel is read/write, but this may not be the case for embedded OLE objects. Is there another way? Does anyone have any ideas?

View 1 Replies View Related

Embedded For Next Loop Based On Value

Feb 18, 2008

Ok, say I have a table with the following fields, custid, mainnum, subnum
whereas the data would look like this:
Code:Cust Main SubA101 1 2A101 2 1A101 3 4A101 4 2A101 5 3B202 1 2B202 2 4B202 3 1C303 1 2C303 2 1

The main number always counts up from 1 on each new customer, I already have code to do this. I am running a for next loop based on the highest main number (I already have this part), for instance, for Cust A101, the for next loop would essentially be: for x = 1 to 5.... like I said, I have this part.

What I want to do when x is counting, to pull up the mainnum value that equals the value in the for/next (in other words equals X), and run a for next loop based on the sub number based for that record. For example using C303's data:
Code:For X = 1 to MaxOfMainNum '<---I already have this 'code to pull up subnum where mainnum=X For Y=1 to SubNum_For_Selected_Mainnum 'I will be doing an insert to table here custid,x,y Next YNext X
The parts in Bold in the above code is what I'm needing an answer to.

I tried to be as concise as possible, but I know it might be confusing.

Thanks in advance,
Benji

View 2 Replies View Related

Forms :: Embedded Value Into Table

Mar 31, 2013

I have a form with a calculated time difference between a "Start" and "Stop" time I have managed to get the correct formula however I would like to display this value in a more civilized forma as HH:MM on the example attached I have some values that appear as 4:8 and it should be 04:08.

The second problem is I need to embed this calculated value on my tblTime as doing so I can run more easily some queries.

View 9 Replies View Related

General :: Embedded Excel Files

Feb 14, 2013

pretty Excel sheet full of conditional formatting that is used as a vehicle assignment board where I work. I know I can embed the file into an Access form using an unbound object, and therefore "integrate" it into Access, however this creates a copy of the file, and therefore when people change the sheet, the original Excel file is not updated. Is there any way to embed the excel file in such a way that the Excel document itself is also updated? Basically some sort of an active, two-way link rather than what amounts to just importing a copy of it? I need to be able to pull data from various cells in that Excel file so it needs to be kept up-to-date.

View 6 Replies View Related







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