Modules & VBA :: Failure Of Logic Due To Quotation Marks?
Jun 17, 2014
In the highlighted line of code attached I am comparing two strings one from a snapshot recordset and one from an array.
The logic fails, you can see in the watches window both values equal "DESIGN and the elseif statement should execute but it does not.
The only reason I can see for this is that the values are equal to "DESIGN and not DESIGN. Where this single quotation mark comes from I do not know. It is not visible in the tables and it does not appear when the values are printed into excel.
I created a query in Query Builder which contains a DCount with multiple parameters and it runs as it should. I am trying to convert it to VBA, but my inability to put in the quotations marks correctly is frustrating me terribly.
Here is the SQL version from Query Builder:
UPDATE [Daily Status Update Table] SET [Daily Status Update Table].NumberOfChases = DCount("[ChaseOtherID5]","[Chases_View_ALL - TX_Mbr 9 Digit]","[ChaseOtherID5] = 'U - Initial Contact' AND [ChaseStatus] = 'A'"), [Daily Status Update Table].ChaseStatus = "A", [Daily Status Update Table].NewStatus = "A", [Daily Status Update Table].ChaseAssignment2 = "Unscheduled" WHERE ((([Daily Status Update Table].ChaseOtherID5)="U - Initial Contact"));
I found this code on a website that uses a form to search all tables in my database. Problem is that when I click "search" I get an invalid argument error. I am guessing that there is a problem with my SQL string. Missing space? Missing quotation marks? etc etc.... Anyway, here is the code:
Dim tdf As DAO.TableDef Dim fld As DAO.Field Dim rs As DAO.Recordset Dim strSearch As String Dim strTableName As String
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.
I 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.
Hello 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.
I am having a frustrating problem with a report filter I have been using for years. The filter works fine if the data is a number, but I am using it for text this time and it simply will not filter the report because I am unable to have the text display as per below. I need to be able to put a qutotation mark before and after the text so that the report filter will recognise and apply the filter appropriately.
I 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?
I 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])
I am using an access program with a form with detail section. I would like to create auto sr.No when i am creating a new quotation. Using Autonumber didn't work because it start numbering from last quotation.
How 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.
ID | NAME | Progresscomplete | Release 1 | a | 20% | July 2 | b | 65% | July 3 | c | 33% | July 4 | h | 15% | Sept 5 | i | 30% | Sept 6 | r | 5% | Dec 7 | s | 50% | Dec 8 | t | 15% | Dec 9 | u | 20% | Dec
now I want to create a query that shows the Progresscomplete for each task as a percentage for a Release
so the total progress for each release is July 300% Sept 200% Dec 400%
and the current level of progress for each release is July 39.33% (118/300*100) Sept 22.5 % (45/200*100) Dec 22.5 % (90/400*100)
thus the current level of progress for each task within a release is 1 | a | 16.9% (20/118*100) | July 2 | b | 55% (65/118*100) | July 3 | c | 28% (33/118*100) | July 4 | h | 33% (15/45*100) | Sept 5 | i | 66% (30/45*100) | Sept 6 | r | 5% (5/90*100) | Dec 7 | s | 55% (50/90*100) | Dec 8 | t | 16.6% (15/90*100) | Dec 9 | u | 22% (20/90*100) | Dec
I have queries for the first two not sure about the last also not sure if i should have the base of the last output be the current level of progress for each release or the total progress for each release
I have tried this for the last output
SELECT ID, NAME, ((Progresscomplete/Count(Progresscomplete))*100) AS currentprogresslevel, Release FROM TEST_RawData GROUP BY Release;
but get an error "tried to execute a query that does not include specified expression... as part of an aggregate function.
I have a report that takes money values form several records and sums them up in various category's making a list of departments with the total amount of balance for each (positive and negative numbers). I need to categorize the positive values form the negative and calculate the totals of each and the grand final total (invoice style)
I created a db, tested it with dummy info and all was fine. I then imported live data from another db. When I came to add new records, discovered that, in the table design, a default value of 0 (zero) has been entered for fields where a look up table is used (where I had used Autonumber for the ID). Solved that easily enough, but later I amended a text box to a combo box and the same thing happened. Why does it do this?
What I need to do is to pull four subsets of data out of this table -
1) A unique list of courses that student x is taking in semester 1 (only) of a given year 2) A unique list of courses that student x is taking in semester 2 (only) of a given year 3) A unique list of courses that student x is taking over 2 semesters (both semester 1 and semester 2 of a given year) 4) A unique list of courses that student x is taking over 2 semesters (semester 2 of a given year and semester 1 of the following year)
Obviously there should be no overlap... and that's where I get a bit stuck
I can craft a simple query that will show me the courses a student is taking in semester 1 of a given year, but I cannot figure out how to get it to exclude those courses which are also still being taken in semester 2. In the above example, the query pulls out records 2 and 4 for student 2; I only want record 4.
I think what I need is to have 2 queries -
a) pulls out all courses for student x in semester 1 for the current year b) pulls out all courses for student x in semester 2 for the current year
- and then subtract the results of query (b) from query (a) (ie remove the rows that are matched). Is there any way to do something like that?
Or is there a simple part of query syntax that I can use to make a query that just says "pull out all the courses for student x where semester=1 and there is no row for this course and this student where semester=2"?
Maybe someone knows the logic behind MS Access for the display of percents in the formating..... ie.... If you input 3 you get 300.00%. I really dont think ANY user would look at a form... with a field called...say "Commission percent" and expect an input of a 5 to return 500.00% Does this need to be corrected with coding?
Im trying to run a query that looks in a table to see if product a and product b were purchased and then return the id but i never get any data, and I know there are some results that should be showing up. If I remove either one of the products it returns data but not when both are requested. here's the query: SELECT [Copy Of data].id, data.purchase_product, data.purchase_product FROM data INNER JOIN [Copy Of data] ON data.email = [Copy Of data].email WHERE (((data.purchase_product)="ProdA") AND ((data.purchase_product)="ProdB"));
I have started a type of CRM database in which all my customer information and product info is stored.
In order to create quotations for each customer, I want to be able to search and select products by product ID, adding quantities if necessary, to be printed as a report.
I have been working on customizing MS ACCESS to produce a report that involves one mathematical computation from my input data. However, I've run into a problem because I can't get the program to carry out a logic command that would work fine in MS Excel: the "IF" command. Specifically, what I'm doing involves a calculation, where one data input is subtracted from another data input, but if the difference is a negative number, I would like to substitute the value of ZERO. The closest I've come to achieving this is to enter the condition ">0" while in Query Design mode, but the problem with this approach is that if the difference is a negative number, the report does not display the data that resulted in the calculation of the negative number. In other words, I want the data to be included in the report, but I want a "Zero" to be substituted for all calculations that produce a negative number. For example, if I enter a set of data, and the two numbers that get subtracted are 40 minus 36, then the difference is positive 4, and so I want a "4" to be displayed; But if the two numbers to be subtracted are 36 minus 40, the result is negative, and so I'd want the report to display a "zero" in the calculation. Please advise me on how to set this up, as the only approach I've found resulted in all negative calculations being omitted from the report. Thanks for your help.
I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????
I need some help with code. I can do this in Excel, but I am not sure how to do it in Access.
Here are the fields:
PoundsPerBox PoundsPerOrder BoxesToOrder
Here is the scenario: If a customer orders custom paint for their equipment, we need to total how many pounds of Powder Coat paint it takes to paint all the units (PoundsPerOrder). When we order this from the vendor we have to order it by the box which is measured in pounds. In this example, the vendor will sell us a box of white Poweder Coat paint in increments of 55 pounds. (55, 110, 165,...)
Verbally, the logic reads like this: If PoundsPerOrder is less than or equal to PoundsPerBox, then BoxesToOrder equals 1. This logic needs to be tested for each increment value (indefinately) of PoundsPerBox.
Am I making sense? How do I accomplish this in Access? And, as a side note, I need to capture and store this value in a table for future reference.
I would like to create a report based on a query. The first part is simple enough. However within this query I have a 'Count field' (a total of the number of duplicated this record has appeared in the table). And would like to create a subreport (if possible) based on that value for example. if count > 1 then show subreport.
My question therefore; is the above possible? How would I go about linking this logic with the subreport
