Select @var From Not Working
Dec 16, 2007
Hello all!
How can I create a query that the select depends on a variable that the user introduces?
would it be something like:
Select [@var] from table ????
it is not working. Example:
@var would be number from 1 to 31 (according to a day on the month that the user wants to view), then the result for it, is the number that the user introduced, instead of the vaule kept in that number...
Hope i make myself clear....
Thanks in advance!
View Replies
ADVERTISEMENT
Dec 4, 2005
sql = sql & " WHERE (((tblAccountBalance.DateLastPayment) < #" & reminderdate & "#))"
Im trying to select all the entrys in a table where the date of the last payment is before a variable 'reminderdate' this works fine if both dates are in the same month ie, DateLastPayment = 26/11/05 and reminder date = 30/11/05, but if the DatelastPayment is 26/11/05 and the reminder date is 01/12/05 then nothing is found.
What am I doing wrong?
View 7 Replies
View Related
Sep 16, 2007
I have two tables naming
1.) Criteria and 2.) IndicatorData.
Criteria table has (CriteriaID, CriteriaName,CriteriaValues) Fields. Example( CriteriaName-'Gender 'and values would be 'Male' and 'Female').
IndicatorData table has fields such as (IndicatorDataID, IndicName, Criteria_Code1,Criteria_Value1,Criteria_Code2,Crit eria_Value2,Criteria_Code3,Criteria_Value3).
I made the relationship between the IndicatorData and the Criteria as 1 to many where Criteria_Code1,Criteria_Code2 and Criteria_Code3 ...... up to Criteria_Code5 fields of IndicatorData table reference to the CriteriaID of Criteria table.
I made the sql qury to get all Criteria Names belong to a particular IndicatorDataID but it didnt work. my query is
"SELECT Criteria.tCriteriaName
FROM Criteria INNER JOIN IndicatorData ON (Criteria.nCriteriaId = IndicatorData.CriteriaCode5) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode4) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode3) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode2) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode1)
WHERE (((IndicatorData.nIndicatorId)=9));"
IS IT POSSIBLE TO GIVE THE RELATIONSHIP BETWEEN INDICATORDATA TABLE AND CRITERIA TABLE LIKE THAT OR DOES MY SQL QUERY HAVE SOMETHING TO DO WITH IT...?
answer would be greatly appriciated.
Thanx + Regards
Kosala
View 1 Replies
View Related
Sep 16, 2007
I have two tables naming
1.) Criteria and 2.) IndicatorData.
Criteria table has (CriteriaID, CriteriaName,CriteriaValues) Fields.
Example( CriteriaName-'Gender 'and values would be 'Male' and 'Female' and CriteriaName-'AgeGroup1' and Values-'<30','30-40','40-50','+50').
IndicatorData table has fields such as (IndicatorDataID, IndicName, Criteria_Code1,Criteria_Value1,Criteria_Code2,Crit eria_Value2,Criteria_Code3,Criteria_Value3, IndicValue).
Example-(IndicatorDataID-'ind001', IndicName='No. of participants', Criteria_Code1-'Gender',Criteria_Value1-'Male' and IndicValue-200.)
I made the relationship between the IndicatorData and the Criteria as 1 to many where Criteria_Code1,Criteria_Code2 and Criteria_Code3 ...... up to Criteria_Code5 fields of IndicatorData table reference to the CriteriaID of Criteria table.
I made the sql qury to get all Criteria Names belong to a particular IndicatorDataID but it didnt work. my query is
"SELECT Criteria.tCriteriaName
FROM Criteria INNER JOIN IndicatorData ON (Criteria.nCriteriaId = IndicatorData.CriteriaCode5) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode4) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode3) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode2) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode1)
WHERE (((IndicatorData.nIndicatorId)=9));"
IS IT POSSIBLE TO GIVE THE RELATIONSHIP BETWEEN INDICATORDATA TABLE AND CRITERIA TABLE LIKE THAT OR DOES MY SQL QUERY HAVE SOMETHING TO DO WITH IT...?
answer would be greatly appriciated.
Thanx + Regards
Kosala
View 1 Replies
View Related
Jan 29, 2008
I'm very new to Access 2000. I'm working with 3 tables.
I finally got this sql statment to work:
SELECT [tlkp.Language].[LangID],
[tblRawData].[LangName],
[tblApplication].[AppID]
FROM (tlkpLanguage INNER JOIN tblRawData
ON [tlkpLanguage].[LangName]=[tblRawData].[LangName])
INNER JOIN tblApplication ON [tblRawData].[AppID]=[tblApplication].[AppId]
How can I change it to UPDATE?
I want to update the tblApplication.LangID = tlkpLanguage.LangID using the joins described in the select statement?
There is no LangName field in the tblApplication.
I have tried everything and I keep getting syntax errors.
View 1 Replies
View Related
Nov 8, 2014
I have a select clause, it executes fine, BUT the totamt is not calculated. The field is Amount. The code is not working.
strSQL = "SELECT Account, Dept, " & "Sum([Amount])" & " As [totamt] FROM TB201410 GROUP BY Account, Dept;"
View 5 Replies
View Related
Aug 22, 2014
I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
View 1 Replies
View Related
Dec 27, 2014
I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.
The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?
View 1 Replies
View Related
Apr 23, 2015
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
View 2 Replies
View Related
Jan 15, 2015
I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
View 13 Replies
View Related
May 6, 2014
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
View 1 Replies
View Related
Aug 28, 2004
Hi,
is there any (reasonably simple) way to select or deselect multiple items from the List Box with individual clicks without using Ctrl key. Eg first click on an item would select it leaving all other items as they are, subsequent click on the already selected item would deselect it etc. I hope this is not too confusing and I would appreciate some help.
Thanks!
View 1 Replies
View Related
Aug 19, 2005
i have a multiselect listbox in my form.
The multiselectlistbox contains the names of different persons from tblUsers.
it's allready possible to write the id's of the names to another table (tblPresent).
But what I can't manage to do is re-select the values in another multiselect listbox. This multiselectlistbox is located on my editform.
I can display the values using a valuelistbox, but i need to see the non-selected items too..
hope someone can help me out
View 1 Replies
View Related
Sep 5, 2014
When I run the below code I am getting the error "End Select without Select Case" I figured it might be because I have the "End Select" before the "End With" however when I move the "End Select" after the "End With" I get the error "Loop Without Do".
Code:
Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
[Code].....
View 3 Replies
View Related
May 11, 2014
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
View 3 Replies
View Related
Jun 28, 2006
This is driving me nuts guys...
I need a query to show only those Company's that have a relationship to ALL Departments. These are the tables:
tblCompanies
CompanyID
tblUserDepts
UserDeptID
tjxCompanySubscriptions
CompanyID
UserDeptID
tjxCompanySubscriptions is a junction table that defines which Department has a subscription to a Company.
I need to know which Company's are subscribed by ALL Departments.
Probably simple, but not for me!
Scott
View 2 Replies
View Related
May 10, 2005
Hi all,
I'm making a simple query to tell me how old a list of members are in a database.
I figure as I have their birthday, armed with todays date access should be able to tell me (in years only) how old they are.
Any help is appreciated
View 14 Replies
View Related
Jul 19, 2005
Hi,
I have 2 computers (computer 1 with Office XP and computer 2 with Office XP SP1). An MDE file created from an MDB file on computer 1 will not work on computer 2 (run time error), but an MDE file created on computer 2 also does not work on computer 1 (error message was "database corrupted").
Does anyone know why and what I can do to make the MDE work on both machines?
View 6 Replies
View Related
Jul 21, 2006
Hi. I have got a small database, I have split it into front and back end. I then try to make an MDE out of the front end and after going to tools>db utilities>make MDE file, i got the save as box open up and i pressd save to my desktop,the box disappears and the screen is just blank(like there is no database file has been open,at the bottom of Access it says Make MDE/ADE.
And it just crashes.. it doesnt come up with any errors.
If anyone has any information pleeeeeaaaseee help.......
View 8 Replies
View Related
Aug 2, 2005
My Combo Box works and displays new entries in text boxes in my form. The selection appears in every record. I would like the selection to be stored only in the displayed record and not for each and every record. Any ideas?
My combo box works on an existing query( Qry1). I would like the data from my query (Qry1) to be displayed in the record (in Form1) which I am viewing. When I go onto a new record (IN Form1) I would like to make a new selection from my query (Qry1) and then store these details in the new record in Form1.
I am a little frustrated - please help.
View 3 Replies
View Related
Sep 20, 2006
This seems so simple, but, being a novice, I'm scratching my head.
I'm using an unbound text box in the form footer to sum the value of a column in the form ( =Sum([LineTotal]) ). [LineTotal] evaluates correctly, yet the sum is always #ERROR. Is there something I am missing, or am I correct in thinking that this should work?
Thanks to anyone who can shed some light.
-Don
View 7 Replies
View Related
Nov 8, 2004
I open a form that is bound to a linked SQL table. On that form a have a list box that shows a series of records based on a query. The listbox DOES contain the PK field "Record_date" in its query.
I am expecting that if I click on a given record in the list box the form would change to that matching record. What am I doing wrong ?
List box code:
Private Sub List212_AfterUpdate()
' Find the record that matches the control.
Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[Record_date] = #" & Format(Me![List212], "mm/dd/yyyy") & "#"
If Not rst.EOF Then Me.Bookmark = rst.Bookmark
End Sub
If I change the form record and click the list box, its always takes me back to the 1st record - not the matching record selected in the form.
Help ?
View 3 Replies
View Related
May 8, 2005
Hy,i've got three tables:
products:
product_id,product_name
months:
month_id,name_of_month
calculations:
calculations_id,month_id,amount
Also have this query:
SELECT DISTINCTROW products.product_name, Sum(calculations.amount) AS [Sum Of amount]
FROM products INNER JOIN calculations ON products.product_id=calculations.product_id
WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value)
GROUP BY products.product_name;
My problem lies in this part of query :
WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value).
I've got problem when i want to get values from my textbox on my form.
If i instead (forms!frmMyForm!txtMyTextbox.value) put (1,2) my query works fine,but when i wanna get this same values from textbox on my form it returns me an error.
I have also noticed one more thing:If i put in my textbox just value 1 it works fine,but if i use more values separeted by comma instead of result it returns me an error.
Why is this,and how to avoide this problem?
Thanks!
View 8 Replies
View Related
Jul 29, 2005
Private Sub propertypass_Click()
If [Forms]![UpdatedFullInventory]![PAM_ID] = [Tables]![UpdatedPropPassInventory]![PAM_ID] Then
MsgBox "A property pass for PAM ID " & PAM_ID & " has already been created. Please search the property pass database for more information!", vbOKOnly, "Error!"
End If
My debug message says
Microsoft can not find the field 'l' referred to in your expression.
View 1 Replies
View Related
May 13, 2005
Hello,
For osme reason this MS Access database I'm working on will not let me debug it. First off, even if I mistype a variable name it is not breaking and giving me an error message. I checked and I do have 'Option Explicit' set as well as "Error Trapping - Break on All Errors" in the Tools - Options - General.
Also, when I set a breakpoint in my code, it doesn't work either.
Any ideas as to what is going on??
Thanks,
Brian
View 1 Replies
View Related
Jun 23, 2005
Our database is 1.6gb in size.
It has stopped working, is there a way to delete old records?
We are told it will crash the computer, because access needs the same space as the data its self.
Any ideas?
Thank you for your time in reading this post
View 2 Replies
View Related
