Queries :: Isolating A Field With Quote Marks?
Oct 24, 2013I am trying to isolate a field with quote marks (").
WHERE (((dbo_AC_ECONOMIC.SECTION)="));
but that does not work..
ADVERTISEMENT
Remove Quote Marks From Imported Table
Mar 16, 2008I 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?
Queries :: Calculated Field - Converting Percentage Marks With Grades
Mar 11, 2014The calculated field converts all percentage marks perfectly fine with grades except 100 which returns to a stupid "E" . I've been trying all sorts and now give up.
Code:
Grade:IIf([Percentage]<20,"U",IIf([Percentage]<30,"G",IIf([Percentage]<40,"F",IIf([Percentage]<"50","E",IIf([Percentage]<60,"D",IIf([Percentage]<70,"C",IIf([Percentage]<80,"B",IIf([Percentage]<90,"A",IIf([Percentage]<100,"A+")))))))))
Queries :: How To Insert Comments And Notes Using Singular Quote
Feb 5, 2015I 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?
Quotation Marks On A Report Field
Oct 13, 2004Hello I am trying to design a CD label 'report', it's going well except that the title field of our productions has to print with quotation marks around it'. The name of the field is, yes you guessed it, TITLE. I know it must be a simple solution but I'm at a loss to find it or understand it. I'm just a dumbo. Thanks for your time and expertise.
View 2 Replies View RelatedModules & VBA :: Strip Out Punctuation Marks From A Field
Jun 26, 2015How can i strip out punctuation marks from a field that i am calling in a writeline command? There is a comma in the name (vendor name) and it throws off my import into my accounting program.
View 4 Replies View RelatedQueries :: Excel Data With Quotation Marks To Access Query
May 15, 2014I have around 3000 numbers in an excel sheet. I need use those numbers in a query (Field Name: Package_Qty), the data type is text.
I need to add these numbers with " ". Someone told me to use ^ symbol and something else, I forgot.
Reports :: How To Include Quotation Marks Around The Field In Design View
Jul 1, 2014I have a table in Access 2010 containing the results of a survey. One of the fields in table is the actual quotes from customers who gave direct feedback. I have created a simple report containing all of the quotes. My manager would liek them included in a summary Word doc but wants quotation marks around every quote. It's too many to manually after the report is exported to Word. Is there a way to include quotation marks around the field in the report design view?
View 3 Replies View RelatedQueries :: Search By ASCII Codes - Records Have Double Quotation Marks And Carriage Returns
Jun 27, 2014I import data through different files into a table, the data comes in different formats. I have a problem sometimes some records have double quotation marks and carriage returns. I know how to replace them as
Carriage Returns
Replace([FieldName],Chr(13) & Chr(10),""
Double Quotation Marks
Replace([FieldName],Chr(34),""
But if i want to use an iif statement because in the field there could be a Double Quotation Mark or Carriage return then it does not find the record
iif([FieldName] like Chr(34),Replace([FieldName],Chr(34),"",[FieldName])
Quote Generator Help
Feb 28, 2006I'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
Quote Form
Feb 17, 2005I 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
How To Eliminate A Double Quote
Nov 13, 2007I 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
Single Quote Glitch
Jan 6, 2005Hi,
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
Single Quote Dilemma
Aug 31, 2003i 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
Quote/Invoice Access Database
Aug 25, 2004Hi
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
Designing A Proposal/Quote Reporting Database
Feb 2, 2006As 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:
Table Layout For Airport Minicab Quote System
Jan 3, 2006ok 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.
Parameter Query With An Embedded Quote String Problem
Sep 19, 2006Hi 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
Running Average Quote Based On Part Number
Apr 28, 2005I'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.
General :: Create A Form That Allows To Search For Particular Quote Numbers
Jul 8, 2013I 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.
Summary Of 5 Marks - MIN And MAX
Feb 25, 2012I try to make my second database in Access. I need to summary 5 numbers, and then minus the lowest and the highest of them. For example (1+2+3+4+5)-1-5=9
In excel it looks like this: =SUM(C2:G2)-MAX(C2:G2)-MIN(C2:G2)
In access (Expression builder) i made this code: [J1]+[J2]+[J3]+[J4]+[J5] - it's the summary of five marks, but how can i erase the highest and the lowest one?
Removing Quotation Marks ....
Dec 6, 2005Hi,
This is probably real easy to do, but ........ I have a field in Access that looks like this:
"12:12:01 PM 12/5/2005, 5:00:01 AM 12/6/2005, 7:00:25 AM 12/6/2005"
From this string, all I want is the last Date entry (mm/dd/yyyy). Thus in this result, it would be 12/6/2005. I know how to use the Right(), Left() and Mid() functions, but I do not want quotation marks included in my result.
Any help would be great ......
SQL Statement Quotation Marks
Nov 4, 2007I have a statement which is driving me nuts...i'm a beginner with using SQL:
Private Sub Command45_Click()
Me.Graph0.RowSource = "SELECT (Format([Date],""" """mmm""" ''yy')) AS Expr1 FROM Chart_Unsafe_Daily GROUP BY (Format([Date],'mmm'' '''yy")), (Year([Date])*12+Month([Date])-1);
End Sub
The statement is RED for an error, i'm sure it is because the quotation marks within the statement are incorrect.
Can anybody help me out?
Accent Marks & Foreign Characters
Oct 7, 2005Hello,
I'm having an issue with foreign language characters (that have accent marks) not being accepted in our database input fields when a user tries to enter them into the textbox. i've enabled foreign languages, checked help menus, used the character map in accessories, and even tried using the alt+# method but to no avail.
since our company works with many foreign countries, many of the firms names have all sorts of crazy accents (à ã ç ö š ǐ ø) and as you can see from that example, one already showed up incorrectly as a box instead of an "i" with the "u" looking accent above it. I've attached a screenshot to this post so you can see what i'm talking about, however in the example it's the "s" with the "u" looking accent that doesn't show in access, as opposed to the "i" replaced by a box in this post.
My question is, is there any way of adjusting our system so as to make sure all accents are allowed? If you also have a list of steps that I can follow, I can try to redo it your way since the ones I've tried have not worked.
Any help would be greatly apprecaited. Thanks in advance!
Forms :: Quotation Marks In A Textbox
May 15, 2013How do I put " " into a form text box?
View 8 Replies View RelatedBug: Treeview Loses Check Marks On A Tab Control
Jul 18, 2006If anyone wants to test this out to verify its a true bug it would be helpful:
Create a tab control.
Place a treeview control (with checkboxes = Yes) on one of the pages.
Populate the treeview.
Check some boxes.
Move to the next page of the tab control.
Return to the page with the treeview.
Viola! The checkboxes you checked previously are all unchecked.
(Not just visually, but property-wise as well).
One step forward, two steps back.
Actually, the work around is to remove the treeview from the tab control and then change the Visible property of the treeview to False if on a different tab page, and True if on the page you want it to display on. But c'mon why can't it work the way you'd expect it to work!
Jeff