i have this qry field that works fine if my formulaID is a NUMBER field.
UomUsageSum: Val(Round(DSum("nz([UomUsage])","[tblFormulaDetail]","[FormulaID] = '" & [FormulaID] & " "),4))
However, i just changed the formulaID to TEXT in my table.
So I think i need different quotes somewhere at the criteria end of this qry calc because FormulaID is now TEXT.
how do i fix this at the end of the field?
thanks
I wondered if someone could explain something for me.
In SQL the difference between ' Single Quotes and " Double Quotes and when and when not to use either.
How do you get Rid of quotes
I have this following code:
---------------------------------------------------------
a = "MPI_CORE_DATA"
b = 'Need something here to get rid of the quotes in a
DoCmd.RunSQL "CREATE TABLE " & b
---------------------------------------------------------
How can i get rid of the quotes (") in a e.g. so "MPI_CORE_DATA" will become MPI_CORE_DATA
I have a nice little app that works very well except that the code is not tolerant of single quotes in the user's data. My problem is that VB uses double quotes for its literals and SQL uses single quotes so where does that leave me?
The user has entered a comment into a data field and I am now adding this to the record on the database. I don't know if the user's data contains a quote. If it doesn't then all is well. If it does then, crash.
Here is a code sample...
Dim SQLStr As String, Ret As Integer
SQLStr = " UPDATE MeetingRole " _
& " SET MeetingRole.Comment = '" & [Forms]![Comment]!Text2.Value _
& "' WHERE MeetingRole.DateOfMeeting = '" & [Forms]![MeetingStatus]![List0] _
& "' ;"
CurrentDb.Execute SQLStr
Anyone know of a standard solution?
Is there a way to generate a string for a text field that has Quotes in it.
I want to generate a string for a text field that has the following text in it.
The "ShankServer" has passed the test.
I need the Quotes (") to be in the text and of course the following will not work.
Me.GeneratedCode = "The "ShankServer" has passed the test."
A friend pointed me in the direction of this site, and I sure hope someone can help. I need to build databases that will primarily be used for creating sales quotes. I know my way around, a little bit, but I am having trouble building this database. Would anyone be interested in helping me?
View 3 Replies View RelatedHello,
I have a combo box on a form that simply selects a record from a 2-table query. My problem is that the data is Book Titles, which can contain both quotes and apostrophes, sometimes in the same title. other than eliminating these characters from the records, how do I make the simple vba code for the box work for either quotes or apostrophes in a record? The box works fine as long as the record does not contain these characters. (I'm a big vba rookie by the way, and this code was created by the box wizard.)
Using Access 2003,
Here is the code for the box:
Private Sub cboSelectBook_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[BookTitle] = '" & Me![cboSelectBook] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
TIA for any suggestions!
LMS
I have a button that generates text according to information in different fields. The text has multiple lines and when I select the button to generate the text it puts all the lines together when I need it to do a new paragraph after each line.
Sample:
Me.GeneratedCode = "This is a test for " & Me.ServerName & "." & _
"The server " & Me.TestResults & " the test."
The above shows up in one continues line.
This is a test for shankserver. The server Passed the test.
I what it to look like this
This is a test for shankserver.
The server Passed the test.
How do you guys escape the double quotes in the string..
Code:sRandomNumber = "1234"sCmdLine = "-RandomNumber="" & sRandomNumber & """
I tried this but this doesn't work...
sCmdLine = "-RandomNumber=""" & sRandomNumber & """"
I tried another method which doesn't work either...
sCmdLine = "-RandomNumber=" & chr(34) & sRandomNumber & chr(34).
What I got instead is this..
--snip--
-RandomNumber=""1234""
--snip--
Thanks...
I am assigned to create a customer quote database for our company. Now it is in excel and already have 40,000 records and increasing average 25 records everyday. The way they want to send quotes are:
- The number of items in the quote are varied from 1 o 15.
- Each item has six different prices, depends on the order qty and want to mention in different adjusent lines.
- Each item has different delivery time depend on order qty.
- Company's sales Terms and conditions need to be printed at the end of each quote in separate page. (two full pages)
How do I create the table to accommodate all these fields? Is it possible to accommodate all items/sub items in one table under one qutoe id. I have customer table, product table etc ready and little confused to create the Quote table.
Any Single line of code available?
View 2 Replies View RelatedI am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code:
Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph
Could someone tell me if I need to break this select statement down? I'm running into problems with the DMax statement since it requires double quotes around it's arguments. When the parser hits the first argument, it closes the string like it's supposed to, but not the way I want it to.
In essence, the query returns 4 fields: unique ID, week number, computed score (TotSum), and the computed handicap (which is half the difference between your score and the high scorer):
Quote: strSQL = "SELECT tblRoster.HEDR, tblScores.WeekNo, " & _
"[A1T1]+[A1T2]+[A1T3]+[A2T1]+[A2T2]+[A2T3]+[A3T1]+[A3T2]+[A3T3] AS TotSum, " & _
"Round(((DMax("[TotSum]", "[qryHandicap]") - [TotSum]) / 2) + 0.1) AS Handicap " & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _
"WHERE (((tblScores.WeekNo) = " & inpWeekNum & ") And (TotSum > 0)) " & _
"ORDER BY TotSum DESC;"
What would the proper syntax be and/or is there a better way to obtain this information. After acquired, it is being salted away in a table which is recomputed after each competition.
Thanks for the input.
-Brian.
p.s. I know storing computed fields is a big no-no, but if you do not compete in a given week, the handicap from the last time you participated is used. This table will store the participants handicap as well as the last competition they were in. It seemed easier this way.
On an existing DB i have set a default value for a field. The customer has now asked that they be able to change this default. I thought, hey, no big problem. They have SuperUsers, so i included a little pop up form that lists all the current valid values for that field (from a look up table). If the default value needs to change a superuser can access this and select the new default. That works fine. The trouble comes when setting the default value on a form.
I figured a simple bit of VBA in the OnOpen event - Me.cmbTypes.Value = Me.cmbTypes.DefaultValue - should work. However the value it returns from the default is ""xxxx"" and as such it is read as "". I can work around it by removing the extra " " but WHY does it do this? Would it be better to set this up as a custom property of the DB?
Using Access 2003
Hi, thanks in advance for any help you can offer. I've got a table that has
Date
Time
Tag ID
Power Level
throughout the day a computer listens to several tags (transmitters) and records the power level of the signal generated by the tag each 3 seconds. What I'd like to do is build a query that gives the Date, Time and Maximum Power level reading for each tag ID. I only want 1 record per tag per day
I've tried using "group by" and max in the query but this gives me all the times throughout the day.
anyway, thanks again for looking
cheers, Shaun
My db contains an unlinked form that is used to append records to 1-3 separate tables (tests to see if master records exists, appends if needed, moves on to child records). One of the fields is a text field meant for notes. The VBA errors out whenever this text contains apostrophes or quotes.
Is there a simple way to recall the string field with quotes and apostrophes intact and append it, or will I have to validate and remove them from the string?
I'm having problems with quotation marks in a sql statement. The string is an array separated by a semicolon.
120/80;70;5'6";125
this string represents patient vitals. I'm using the string to update a record. But I get hung up with the quotation mark.
I've tried: 120/80;70;5''6'"';125 which is a enclosing the quotation mark with apostrophies, but this does not seem to work. The sql still gets hung up. My sql statment looks something like:
original string: 120/80;70;5'6";125
strPreOpVits = "120/80;70;5''6'"';125"
mysql = "UPDATE mytable SET PreOpVits = '" & strPreOpVits & "' " & _
"WHERE nID = " & myRecID
docmd.runsql mysql
I've narrowed it down to the quotation marks and I'm unsure how to handle these. I get a runtime 3075 - Syntax Error.
Here is the code that I use to convert the original string
Public Function FixQuotesInSql(strToFix As String)
Dim lgth, y As Long
Dim strTemp, char2Add As Variant
'This routine fixes the use of apostrophe and quotation marks in an SQL sequence
'If the apostrophe is at the beginning or end of the string it replaces with 3 x "'" or "'''"
'If in the middle of the string then replaces with 2 x "'" or "''"
[Code] ....
Error I'm getting 'The expression you entered contains invalid syntax, or you need to enclose your text data in quotes.
Code:
If Dcount(Nz([StatFlag]),[Books],[StatFlag]="W") >0 , ((Nz([StatFlag])) ="W" , (((Nz([StatFlag]))<>"R" And (Nz([StatFlag]))<>"M" And (Nz([StatFlag]))<>"H" And (Nz([StatFlag]))<>"P") AND ((Authors.Status)="A"))
How do I make quotes and invoices submittable with no product and service details? We use sale book tags and have a few tags that are voided. I want to keep them in our records as voided. I've create new quotes for these tags, labelled them as void through products and services, but I can not submit the quote. I think I can not submit the quotes because there is no Total Price amount. How do I submit quotes and then invoices for my records with no Total Price amounts?
View 2 Replies View RelatedHi,
I need to make a new table from an existing table. Each field in the output table must have a leading and trailing quote. I tried to surround the quotes with quotes (" " ") Acess says it's invalid.
For example: NewOutput: " '' " & [CustTable]![FirstName] & " " ".
This does not work.
Any ideas.
Thank you,
John
Hello:
I am linking to a txt file from an Access DB. I am trying to write a query that will return a field that parses the leading and lagging "s that are around the name in the name field, e.g., "don smith". What I need in the revised field is simply, don smith. Because I am linking to the file, I only have read access and thus I cannot delete the "'s with vba from a form.
Thank you very much for your assistance,
Hey
I am looking for a way to secure a single DB. I would like to be able to use the DB on other PC, but still have user control.
I have found that when using the MS Access User and Ground way will only secure DB's used on the PC it was set on. If i take the DB to my home PC i am not promted for a Password and i can go mad with information i would like to protect.
Am i doing something wrong when setting the security? :confused:
Shot
Ok, as the title suggests I'm having some trouble making a many to many relationship work using a single sub-form. This is really more of a lookup/combo box question if you think about it.
Here is the situation: I have three tables, ComputerPrinter, ComputerComponent, Components
ComputerComponent is actually a joining table between ComputerPrinter and Components, with some extra fields which differentiate a specific instance of a component.
It also has it's own autonumber primary key as one ComputerPrinter can have more then one of the same component (i.e. my boss has two of the same monitors). Its foriegn key to the Components table is a lookup to the ComponentDesc(Component Description) field. (I know a Lookup in a table? He's a witch!, burn him! )
I have a query that Joins the ComputerPrinter table to the ComputerComponent table, and that is the basis of my sub-form (the main form is bound to ComputerPrinter).
This all works, you can select any ComponentDesc and it will add that component to ComputerComponents, thus a many-to-many done through one sub-form (albeit limited). The problem comes in that I do not want the user to add just any possible component, but rather only components that are not in use (i.e. have no computer or printer attached to them). I have a query that gets these components, but when I change the row source of the combo box to just those that are not in use, all the components that are attached to the computer (or printer) that is currently selected on the main form, and that do not have an instance that is not in use (there is no spares in the inventory) disapear!.
This is obviously due to the NotInList property, so by making the bound column the first, and changing it's width to .1 inch, I though that it might work... instead I get just the componentID's (the primary key of the Components field) and the text column (despite have a width of 1") doesn't show up at all (although the drop down shows the text of the not in use components). No luck
Is there a way to show all the component descriptions of the current computer/printer and yet limit the drop down to only those that are not in use? (in a continous form without messing up the other entries that is)
If anyone at least knows if it's possible, I would appreciate the post.
-mwalts
Hello,
I have a table on which i must work at row level, one by one.
I put the table into a recordset and i scroll the table row by row from the top until the bottom. The work process - a test - is executed for each row and returns an integer.
I would like to put the process into a function and simply call the function when i move to the next row.
I defined : Function TestScore(ByVal MyTable as Recordset) as Byte
As it is so slow and the results are not always correct, I wonder if the WHOLE table might be passed to the function, and not only the current row.
Does anyone has an idea on passing a single row from a recordset to a function ?
Thanks
Hi,
On our main menu table, I only want to be able to add one record.
On the form that I have linked to this table, should you press the tab key to go through all the items / buttons, it opens a blank record screen. However, I do not want this. Any ideas?
Thanks in advance
Lee
Hi all. I'm working on a temporary database (Access 2003) for work until we get our system validated. Is it okay or acceptable to have a single-field table or do I need to throw an autonumber on there for the key? I really just need this table to populate a combobox/listbox in the form. I don't want to to use the "OnNotinList" with the combobox so I need to store them somewhere. Thanks!
View 1 Replies View Related