I have an Append Query that I developed and am wanting to use the SQL View statement in a piece of VBA code that I am writing. The problem is that it is not working.
Here is the SQL View when I am in Query Design View.
Code:INSERT INTO tblMasterAccountList ( AccountNum, AcctName, [Account Type], [Billing Spec] )SELECT TEMPAcct.Acct, TEMPAcct.Name, TEMPAcct.Type, TEMPAcct.BillingSpecFROM TEMPAcctWHERE (((TEMPAcct.Existing)=False));
Here is the code I have in VBA...
Code:Dim strSQL As StringstrSQL = "INSERT INTO tblMasterAccountList (AccountNum, AcctName, [Account Type], [Billing Spec]) "strSQL = strSQL & "SELECT TEMPAcct.Acct, TEMPAcct.Name, TEMPAcct.Type, TEMPAcct.BillingSpec "strSQL = strSQL & "FROM TEMPAcct "strSQL = strSQL & "WHERE (((TEMPAcct.Existing)=False));"DoCmd.RunSQL strSQL
The code runs fine in that no errors are generated, but it does not append the records. When I run the query outside of VBA, it does append the records. Is there something special that needs to be done with an action query for the SQL statement to work in VBA?
I have at least 200 excel spreadsheets that get updated every day with closing prices of commodities. I want to run a breakout query against every spreadsheet to see if today's close is a new 20 day breakout. What is the best way to organize the access table or tables? Put all the data in one table or have separate tables for each contract and run one query against the group? I need to be able to update the tables everyday so that today's close info is included. I have a working query that works with a single dynamically linked spreadsheet to table, but I don't want to set this up for all 200 spreadsheets. Can this process be duplicated? I have tried to modify the query to run against two separate tables, but was unsuccessful. Since there are so many potential tables, if that is the best way to do it, how can you say select all tables and then run the query?
Here is the query I have written:
SELECT WZ07.Symbol, WZ07.AlphaDeliveryMonth, DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, WZ07.DClose FROM WZ07 WHERE (((DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2)))) Between Date()-28 And Date()-1) AND ((WZ07.DClose)=(select max([WZ07].DClose) from [WZ07])));
The WZ07 is the December Wheat contract for 2007. This is what the excel spreadsheets are named. I had to do a dateserial command to turn a number into a date(couldn't make access recognize that 20070831 was August 31,2007) All the column headings on all the spreadsheets are the same: Symbol, AlphaDeliveryMonth, DDate(transdate),Close
I know that this is both about tables and queries so I wasn't sure where to post this. Thanks for your help.
i am relatively new to databases so this is a fairly basic question.....
I have a form. Within this form all records are locked as i do not wish users to change details. However i am told there is an option allowing the editing of existing records upon request. is this true?, as it would improve my form greatly
I just bought a new computer. It came with a GIGANTIC (for me) 19 inch monitor.
Here's the question. What resoution should I set the screen to? I make databases for others. If my forms are too big for their monitors they have to use the scroll bars all the time. If my forms are too small it doesn't look professional.
Hi all. I am looking for a formula to calculate a commission, based on the total sales price. The commission is a sliding scale. Ex. if sale price is 200 or less, commission = 30%; if sale price is 201-500, commission = 25% on 201-500 & 30% of 1st 200; if sale price is 501-1000, commission = 20% on 501-1000, 25% of 201-500 & 30% of 1st 200; if sale price is 1001+, commission = 15% on 1001+, 20% on 501-1000, 25% on 201-500, & 30% on 1st 200. Does that make sense? In other words, if sale price is 300, I want commission to be 30% of 200 + 25% of 100, which = 85. Is there a way to write this in one formula?
This report is based on a query. The query is called "Diplomas_requested_per_month". The fields in the query are: "Transcript_type" and "Request_date". In this query there is Between [Start Date] And [End Date].
I am attempted to count the number of "Official Copy" for a specified month.
I am propted to input the start date and ending date, it works great when I run the query. But in the report, I get a "#error".
I am thankful for who have responded to the previous posting of this issue, but I am completely lost.
Using Access 2k and Word 2k on a WinXp Workstation.
The code that I'm using is MS standard and has never previously given me any problems (see below). I've searched the MS knowledge base and Googled the error but nothing I find seems to relevant to this situation.
Can someone help?
TIA,
Mo
-----code----
DoCmd.Hourglass True If CreateWordObj() Then With gobjWord .Visible = True .Documents.Open "c:hp_lettersdrugsranout.doc" DoEvents With gobjWord.ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .Execute End With '.ActiveDocument.PrintPreview .Visible = True End With End If
I couldnt find a search facility to see if this had been covered before, so apologies if it has been answered before.
I have created an application in Access (2002) which from the users side of things will start in a form and give them no access to the back end.
Unfortunately, it is entirely possible that different users will have their screens set at different resolutions and so the application will be unusable on some and too small on others.
I was wondering if it was possible for access to automatically resize according to the users screen resolution.
I have searched the MS Knowledge Base and help files etc, but I think it is looking less likely that it is possible. Creating 5 or 6 different versions for different screen resolutions would be a none option.
How can I make my DB dectect the users computer screen resolution and adjust my forms and reports to it?
What is hapening is that is I make the DB on 1024/168 and than the user uses 800/600 the forms and reports look huge..... Is there a way to fix this automaticaly?
hi i just signed up to this site and need some assisstance from someone
i need to create a form in access 97 that enables me to search for a keyword on a given search area, for example the cd/dvd rom drive for files and folders
After a user completes a form, they click a button at the end that uploads the information the back-end database and creates a PDF copy using the DoCmd.OutputTo function. It has been working fine, but one user just had an issue where the form was very zoomed in and cut off. So I have 2 questions:
1) What is the cause of this problem? Is it based on screen resolution?
2) Is there a way to fix the problem to make sure it does not happen?
Hello this is my first post in the definetely best Access & VBA forum on the net and i hope i am in the correct forum to ask this. I would like to know if there is any way in access to change the size of the for my users view according to their screen's resolution. E.g. i am creting the form using 1280 x 1024 resolution and my users have 1024 x 768 resolution how go i make the form adapt to their res. Thanks for any replies :D
I hope I can explain this clearly. I have a subform that has a lot of information and I was wondering if there is a way to make the subform appear as if one turned up the resolution, thus making it's contents smaller, allowing me to pack more info in it. I would love to leave the size of the main form alone. I'm using Access 2000. The sub form is currently displayed as datasheet.
I created a form which fits to my screen resolution. When I open it through an another computer which has different screen resolution then my form is opening according to my 1st screen resolution.
I wanted to fit the form resolution to the desktop on which it it opening. Is it possible?
How to set the form the screen resolution on which the form is opening.
I am using an unbound object frame to display an .png image file which is set in vba.
The image is an excel chart saved as a .png
The problem I have however is that the image I save from Excel is great quality, and is pretty tack sharp, but when I display the same file in the unbound object frame in access, it is not nearly as sharp.
I am setting the picture property of the control as:
ubImageUserChart = "c: empmyChart.png"
It displays alright, but is just a bit fuzzy - still quite legible, but it is a complex graph with a lot going on - has regressions and formulas etc on the graph, and they need to be very clear.
BTW - the unbound frame is the same size as the Excel chart which gets saved as a .png file. If I tile the images (Access unbound frame and original file in picassa preview) side by side - they are identical - size, orientation etc.
It is not practical for me to try and do the chart natively in access as it is way to complex. I am using access vba to drive an excel session to do all the statistical yack work and chart rendering, then displaying a png image of the resulting chart in an unbound object frame in access.
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
I have a text box that I'm trying to control the color of based off an IF then statement. Based off to If's
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - A" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
This part works but I need to add a second one
If [Reports]![Rpt-Paths]![subreport].Report![ModuleCh] = "Ch - B" And [Reports]![Rpt-Run Paths]![subreport].Report![Status] = "0" Then
So I want the text box to be RED if the status of both Ch A and Ch B is 0 but if either one has a status greater than 0 then the text box would be green.
I'm using this code to do some calculations on a form. This code works fine as long as only one IF statment is true. My problem starts when the one of the values is equal (meaning qual is 4) so I try to add < or = to and then I end up with more then one IF statement being true. Is there a better way to do this?
If Me.[qual] < 4 And Me.[completed] < 61 Then Me.[GtoG] = [GtoGtotal] Else End If If Me.[qual] < 4 And Me.[completed] > 61 Then Me.[GtoG] = Me.[qual] + 61 Me.[temp1] = Me.[completed] - 61 Me.[Delayone] = Me.[Delayone] + [temp1] Else End If If Me.[qual] > 4 And Me.[completed] < 61 Then Me.[GtoG] = Me.[completed] + 4 Me.[temp2] = Me.[qual] - 4 Me.[Delaytwo] = Me.[Delaytwo] + [temp2] Else End If If Me.[qual] > 4 And Me.[completed] > 61 Then Me.[GtoG] = 65 Me.[temp1] = Me.[completed] - 61 Me.[Delayone] = Me.[Delayone] + [temp1] Me.[temp2] = Me.[qual] - 4 Me.[Delaytwo] = Me.[Delaytwo] + [temp2] Else End If
