This is not a problem per say, just a debate topic. I can't seem to find anything that discusses this topic. This is in 10g
Is it quicker and more efficient to refresh a materialized view or drop it and recreate it? For example, if you have a mv with a date in the definition, should you hard code the date in and change it everytime you recreate the mv or put sysdate in and refresh the mv?
istya
Last Activity: Jun 6 '08, 09:01 AM
Joined: Aug 21 '07
Location:
-
Refreshing or recreating a materialized view
-
import data from .xls file
I am trying to import data from a 2000 excel spreadsheet, but I am having trouble with transferspreads heet.
It tells me "Couldn't decrypt file"
Can any point me in another direction please? -
help with dates
Can you "add" a number of days to a date?
I know you can use sysdate and do calculations like (sysdate-7), but can you do this on a date ie (01/01/08 -7)? -
if this is in VBA,
ORCode:if nz(fieldB,"") = "" then msgbox("This field cannot be left blank") else if fieldB = 0 then msgbox("You are trying to divide by zero which is not allowed") else variable = fieldA/fieldB end if end if
...Code:if nz(fieldB,0) = 0 then msgbox("This field cannot -
Thanks for that, but I don't think I'm explaining myself terribly well.
I have a table called 'partc' which holds about 20 numbers, i.e. scores on parking, toilets, lockers etc, per building. I want to workout the average of these score per building, but not include the zero values in the average.
ie
(sum of all scores / number of non zero scores) per buildingLeave a comment:
-
There is a diffreence between null and 0. You should see if you are getting nulls or 0, if nulls, try changing them to 0, nz(value, value if null)
Also, your problem may be dividing by 0 which will give an error....Leave a comment:
-
Count IF help please
Hi,
I want to, if possible, create a query which counts the number of non zero entries in a table. I am aware that there is a DCOUNT in VBA, but atm I don't want to go down that route. Can anyone point me in the right direction please?
Thanks -
Run-time error 3061
I am having a dumb day to day. Can anyone have a schufty at my code and see why I am getting the runtime error 3061? I'm working on 2000 if that helps at all.
...Code:Dim dbs As DAO.Database Dim results As DAO.Recordset Set dbs = CurrentDb Set results = dbs.OpenRecordset("SELECT COUNT(*) FROM name_check;") MsgBox (results(0)) 'Set results = dbs.OpenRecordset("SELECT division FROM -
If you do the working around the command button rather than form_load where formname is the name of the form that you are trying to open and command_button is the name of you button
...Code:Private Sub [b]command_button[/b]_click() Dim dbs As DAO.Database Dim results As DAO.Recordset dim stdocname as string dim Dim stLinkCriteria As String Set dbs = CurrentDb Set results = dbs.OpenRecordset("SELECTLeave a comment:
-
post your code and I'll have a look. Also, let me know if you want to open the form and then check or check than open the form if required.Leave a comment:
-
What I suggest you do then is the code for the command button rather than the form_load() sub
...Code:Private Sub command_button_click() Dim dbs As DAO.Database Dim results As DAO.Recordset Set dbs = CurrentDb Set results = dbs.OpenRecordset("SELECT * FROM selcour;") If results.recordcount = 0 Then MsgBox ("There were no records to display") Else 'open formLeave a comment:
-
does it display the "There were no records to display" message or doesn't it get that far?Leave a comment:
-
you want to use results.recordc ount rather than results(0) as you don't have count in the recordset sql string
Code:If results.recordcount = 0 Then MsgBox ("There were no records to display") Else 'open form End IfLeave a comment:
-
One other question - do you have Microsoft DAO ticked in your "Tools->References" in the VBA??
I think I know what the problem is -
rather thanCode:Dim dbs As DAO.Database Dim results As DAO.Recordset
My mistakeCode:Dim dbs As DOA.Database Dim results As DOA.RecordsetLeave a comment:
-
Can you do me a favour and post the whole sub in and I'll see if I can find the problem.Leave a comment:
-
Sorry, one thing I for to say, if you use "SELECT COUNT(*) FROM query;" then recordcount will ALWAYS be 1 as you have a result - its a value of 0, but it's a result. If you use the count function, you can use result(0) instead of results.recordc ount as results(0) will use the value returned by "SELECT COUNT(*) FROM query;"Leave a comment:
-
What date format are you using? In SQL I think you would need to format the date to have this work the way you expect.
Is it the 11th Aug 07 > 10th Sept 07 or 8th Nov 07 > 9th Oct 07?
I'm not sure what the function is for Access SQL but in ANSII standard it's
Code:to_date(sysdate,'dd/mm/yyyy')
Leave a comment:
-
I had this problem, what you can do is
where query is the query that you form is based on.Code:dim dbs as dao.database dim results as dao.recordset set dbs = currentdb set results = dbs.OpenRecordset("SELECT * FROM [B]query[/B];") if results.recordcount = 0 then msgbox("There were no records to display") else 'open form end if
If you put...Leave a comment:
-
passing values from a form to a query
Hi,
Can someone have a look at the below query point me in the right direction please? With the form flood_search open I get a popup box asking for [forms]![flood_search]![osapr] - any ideas?? It's in 2000 and the columns in the SELECT are just to shrink the amount of code
...Code:SELECT AddressPoint.columns, address_details.columns FROM addresspoint left JOIN Address_details ON address_details.OSAPR = AddressPoint.OSAPR
-
Does it have to be a report? You could do something like
Which would give you totals for each state and race but nothing else.Code:SELECT state, race, sum(vlaue to be totaled) FROM table GROUP BY state, race;
Leave a comment:
No activity results to display
Leave a comment: