Hi
I want select 2 table at same time
this is my code and my first table
Code:
strSQL = "SELECT TOP "&Cint(intTopCount)& " * "
strSQL = strSQL & "FROM T_WEBLOG "
strSQL = strSQL & "WHERE b_published = true "
strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"
the above code work very good
but I want select second table
I added this code to above code
Code:
strSQL = strSQL & "SELECT T_IMAGES.* "
strSQL = strSQL & "FROM T_IMAGES "
so this is my code
Code:
strSQL = "SELECT TOP "&Cint(intTopCount)&" * "
strSQL = strSQL & "FROM T_WEBLOG "
strSQL = strSQL & "WHERE b_published = true "
strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"
strSQL = strSQL & "SELECT T_IMAGES.* "
strSQL = strSQL & "FROM T_IMAGES "
but after I added second code to it
I get this error
============
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.
I have a value list combobox (cboShift) on my form with selections as "1st Shift";"2nd Shift";"3rd Shift" and a subform that should filter my records based on the shift selected on the mainform.
My data fields in my query are [TimeStart], [Time Stop], [other fields]. I would like that e.g. when "1st Shift" is selected, it should filter my records based on the [timeStart] that falls within the range, and so on.
I really don't know how to handle this, even to set the time range parameter for this... I have written this function for a start. To continue, i ask for your help or guide. Please help????
Function fShiftWorked(strTimeStart As DateTime) Dim strOperatorStart As String strOperatorStart = FormatDateTime(([tblTimeLog]![timeStart]), vbLongTime) If strOperatorStart >= #8:00:00 AM# And strOperatorStart < #5:00:00 PM# Then strTimeStart = "1st Shift" ElseIf strOperatorStart >= #5:00:00 PM# And strOperatorStart < #12:00:00 AM# Then strTimeStart = "2nd Shift" ElseIf strOperatorStart >= #12:00:00 AM# And strOperatorStart < #8:00:00 AM# Then strTimeStart = "3rd Shift" Else strTimeStart = "2nd Shift" End If End Function
heya all- ive got sql max working on my table beacause i changed a field type to number and then sql selects the maxium value- but i now need to select a specific number along with its record from that same table. This specific number will depend on whats been selected in a drop down box- but when im running the sql its comming up with this error:
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
Could this be to do with the field- beacuse i needed to change it to numeric type as i wanted to use sql max, and what way could i get round it??? Heres my sql command that im running to get a value:
Code:sub sqlCmd( )'==SQLSTATMENT=================================set objrs = server.createobject ("ADODB.recordset")dim sqlStatmentsqlStatment = sqlStatment & "SELECT * FROM [Areas] WHERE [Area_Code] = '"& Code &"' "objRs.OPEN sqlStatment,objConn,1,3'==============================================end sub
I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.
e.g.
I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.
I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.
e.g.
I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.
I have a database spread accross a number of tables, I'll simplify it a bit here - there's questions, answers, respondents each as seperate tables.
Respondents includes RID Name PostCode
Questions QID Question
Answers AID QID RID Answer
The answer table contains answers to all the different questions.
How can I query this to compare questions ? grouping answers to spot trends in the data
So I'd want to pull out the answers for questions 1 and 2 showing someone that answered question 1 with Yes and question 2 with No.
Any help would be much appreciated, or even a point in the right direction. I've played with Crosstabs, multiple queries and pivot tables - none seem to be able to do this.
This must be the easiest of things, but I haven't used access in a while in now in my new job I need to run the following query.
I have to select a number of postcodes from a single table to see how many are in there. For example select postcodes beginning with B, CV, DY, HR, ST, TF, WR, WS, WW. Then I need to write down how many records found with the above postcodes.
Hi, I have 13 tables in all. 2 are Area and Scope which have unique entries of areas and scope and the remaining 11 will have a primary key, Tag_No. I want to select data from these 11 tables such that ALL the data will be pulled out but say table 1 has a column MOC and remaining 10 tables dont, then it will be a blank or null in those columns for the 10 tables. How can this be done? (I hope I am not confusing!!!) Adwait
I want to select at random 4 of these records and append them into another table along with some other data that the query will not pick up, in this case Pilot and Month.
I guess I am having to go the VBA route but dont have a clue on how to do it.
Cheers in anticipation.
Andy.
INSERT INTO Assignments ( flightcode, aircraft, depart, destin, pilotcode, [Month] ) SELECT Schedule.Flightcode, Schedule.Aircraft, Schedule.Departure, Schedule.Destination, [pilot] AS Expr1, [MONTH] AS Expr2 FROM Schedule WHERE (((Schedule.Departure) Like "man*"));
ID |Software ------------- 1 |Office 2 |Winzip 3 |Etc.
I made a form with a dropdown-menu in which I can select the software and it stores it in another Table named TblPC. It stores something allright. but not the text. It stores the ID Nr. How do I get this to work that it puts the text inside the table instead off te ID nr?
I allready tried changing the properties off the listbox but it wouldn't help.
I have a form that runs a parameter query to search for university name and then displays 2 fields, university name and course name.
I am having difficulty with a search button that i have on the form called search_command; it is supposed to run the exact query as when you enter the form, it does this but displays the result in a dataheet, i want it to repopulate my 2 text label fields as mentioned above.
In addition i want then to be able to go to a specific record, select it and then press a button to append it to another table. i ahve not started this part yet
I have to make a multi-user manage database.All the usernames are sorted in one table and exported to listbox in Form.For each user there is a separate table with 3 columns where i must fill information from listboxes.The listbox (there are 4 listboxes, one for users select and 3 for different parameters) are located in MainForm where you select the information.My question is how can I select a specific username(table) from the listbox and fill the information from the other listboxes to the selected one?
The usernames table is named - Clients
each table for separate user is named client1, client2, client3 etc.
the form where you choose what to select is named Fill form.
The listbox for users is named - List71 And the listboxes for parameters are named - List75, List77, List79 Also there are 3 textboxes with calculated fields that i also need to insert in the specific user table.
I am looking for a way to generate a list of all fields within a table, have the end-user select which fields he/she wants to include, and then run the query. I am trying to create this within a form for a nice, easy to use GUI.
I am using Access 2010 on Windows. The fields I need them to select from are in one table, however there are many lookup (tblkp) tables related.
Folks, can someone help me on this? I am not sure why the select query inside this procedure is not working. Here is the that routine:
Public Sub temp() Dim strDocName As String Dim strTableName As String Dim strTbl As String Dim aot As Access.AccessObject Dim strSQL As String Dim rpt As Report Set rpt = CreateReport
strTbl = Forms!frmSearchBoilerGuar!cboTypeOfGuar
For Each aot In CurrentData.AllTables If aot.Name = "strTbl" Then strTableName = strTbl End If
Basically, I am trying to select a table name from the combobox and then use that table name for my query. Then I want to use that query as a recordsource for my report.
I am trying to use a combo box to control which table a data entry form will write data to. I want to create a form that has a combo box to select from a top level table that I will call "Stores". Once a "Store" is selected from the drop down, the next field on the form will be a data entry field. The data entered in that field will be written to the table selected by the preceding combo box.
So, basically I would have say Wal-Mart, Macy's, Sears, K-Mart and etc, listed in my "Stores" table. Once I select one of the stores from the drop down, I would then enter a "department" name in the data entry field and based on which store I selected from the previous combo box, the data would be written to that stores department table (which each store will have its own department table), e.g., WalMartDepts, MacyDepts, SearsDepts, etc...
I have several lookup tables in an Access database. These tables are used to populate fields in the main table and act as filters for viewing record subsets.I want to create a form that does the following:
1.) List the lookup table via a combobox.I was able to accomplish this with the following code:
Code: SELECT MSysObjects.Name AS [Table Name] FROM MSysObjects WHERE (((MSysObjects.Name) Like "tblJob*") AND ((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0)) ORDER BY MSysObjects.Name;
2.) When a table is selected from the combobox, display the table in a subform for updating.
I have setup a simple access database consisting of 3 tables
Volunteer Id (PK) Name
Job Id (PK) JobName
Link Id (PK) VolunteerId (FK) JobId (FK)
There is a one to many relationship between Volunteer and Link and a many to one between Link and Job
I want to create a form from which I'll select a Volunteer Name and then choose a JobName from a list box of options and have it create an entry in link with the id of the volunteer selected and the id of the job selected
So let's say there are 5 entries currently in the Job Table.. let's call the JobNames A,B,C,D and E.
In my form I select my name from the list (sourced from volunteer.Name) and then select B from the dropdown. My desired result would be a single entry gets created in Link with VolunteerId = 1 (representing me) and JobId = 2 representing the selection of B. Problem that I am having is that after I select B in the dropdown it creates a new record for B in Job and the Link table gets updated with that JobId as opposed to simply using the one that was already in the table.
In an Access 2007 module, is there a way of sending an email to a list of people stored as a list in a table in the database, rather than having to put all the names into the function?
I have a function I'm using to generate and send out an email to certain people, but the list is constantly growing so I'm looking for a better way to manage it! The main issue is having to kick users out of the database every time I need to update the recipients list... because it's stored in the code.
If I create a simple table containing all the names, how could I then ask it to use that instead?
This is the function I'm using currently - found on here and adapted to my own purposes
Syntax to use for the function: SendNotesMail "recipients", "Body Message", "Additional Text", "Subject"
Code: Public Function SendNotesMail(strSendTo As String, strBody As String, strExtraText As String, strSubject As String) 'This public sub will send a mail and attachment if neccessary to the recipient including the body text and additional comments from the Active record DoCmd.OutputTo acOutputReport, "REP09emailnotification", acFormatRTF, "x: endersgroup tendering databaseTenderUpdate.rtf", False Dim Subject As String Dim Attachment As String
[Code] ....
So I guess my question is: how do I get my recipients from a table into the strsendto?
I have an on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is : Private Sub cmd_mailreport_Click() Dim office As Object Set office = Checks.office DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _ "info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _ True, "" End Sub
I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields
Purchase Currency Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:
SQL Tried
1. UPDATE Costs SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2. UPDATE Costs SET ExchangeRate = [Exchange Rate] FROM [Exchange Rates 13/14] INNER JOIN [Exchange Rates 13/14] ON Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
3. SELECT [Exchange Rate] FROM [Exchange Rates 13/14] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.
Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
I have staff table which is a lookup field on the phone call table.when the phonecall form opens I want the employee field to auto fill in the employees name from the lookup field according to there security level.so something like this
On Open Select Case Forms!frmLogin!cboUser.Column(4) Case X = 4'the employees security level' Case X = Insert Table_tblPhoneCalls!EmployeeID"4" into Form_frmPhoneCalls!Employee. End Select
of course this is a syntax error as I do not know the correct code words.
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?